Try-Catch in User Defined Function?

Question

I'm trying to write a UDF to translate a string that is either a guid or a project code associated with that guid into the guid:

CREATE FUNCTION fn_user_GetProjectID 
(
    @Project nvarchar(50)
)
RETURNS uniqueidentifier
AS
BEGIN

    declare @ProjectID uniqueidentifier

    BEGIN TRY
        set @ProjectID = cast(@Project as uniqueidentifier)
    END TRY
    BEGIN CATCH
        set @ProjectID = null
    END CATCH

    if(@ProjectID is null)
    BEGIN
        select  @ProjectID = ProjectID from Project where projectcode = @Project
    END

    return @ProjectID

END

This works fine if the above code is embedded in my Stored Procedures, but I'd like to make a function out of it so that I follow DRY.

When I try to create the Function, I get errors like this:

Msg 443, Level 16, State 14, Procedure fn_user_GetProjectID, Line 16
Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

Does anyone have an idea how I can get around this error?

Edit: I know I can't use Try-Catch in a Function, I guess a simplified questions would be, is there a way to do a cast that will just return NULL if the cast fails, instead of an error?

Is using SQLCLR an option ? If so, writing your own SQLCLR function would work. — Moe Sisko
– Moe Sisko, Commented Jul 9, 2010 at 5:05
SQLCLR is definitely not an option. Would be so much easier, yes. I ended up using a combination of my solution and 8kb's. — Moose
– Moose, Commented Jul 9, 2010 at 13:25

DOK · Accepted Answer · 2010-07-08 17:18:48Z

5

Apparently you can't use TRY-CATCH in a UDF.

According to this bug-reporting page for SQL Server:

Books Online documents this behaviour, in topic "CREATE FUNCTION (Transact-SQL)": "The following statements are valid in a function: [...] Control-of-Flow statements except TRY...CATCH statements. [...]"

But they were giving hope for the future back in 2006:

However, this is a severe limitation that should be removed in a future release. You should post a suggestion in this regard and I will wholeheartedly vote for it.

answered Jul 8, 2010 at 17:18

DOK

32.8k8 gold badges64 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moose Over a year ago

Well, yeah, I know I can't use Try-Catch, that's whay I was asking for alternatives. Fosco's method will work as well, but I don't want the error if the cast fails.

Lukasz Szozda · Accepted Answer · 2019-08-25 08:25:52Z

4

I know I can't use Try-Catch in a Function, I guess a simplified questions would be, is there a way to do a cast that will just return NULL if the cast fails, instead of an error?

Starting from SQL Server 2012 you could use TRY_CAST/TRY_CONVERT functions:

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

CREATE FUNCTION fn_user_GetProjectID(@Project nvarchar(50))
RETURNS uniqueidentifier
AS
BEGIN
  declare @ProjectID uniqueidentifier = TRY_CAST(@Project as uniqueidentifier);

  IF(@ProjectID is null)
  BEGIN
     select @ProjectID = ProjectID from Project where projectcode = @Project;
  END

  return @ProjectID;
END

answered Aug 25, 2019 at 8:25

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

8kb · Accepted Answer · 2010-07-09 00:42:57Z

3

From MSDN:

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

By using the NEWID function.

By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f.

For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

You can use pattern matching to verify the string. Note that this won't work for specific encoding that reduces the size of the GUID:

declare @Project nvarchar(50) 

declare @ProjectID uniqueidentifier 
declare @HexPattern nvarchar(268) 

set @HexPattern =  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 

/* Take into account GUID can have curly-brackets or be missing dashes */
/* Note: this will not work for GUIDs that have been specially encoded */
set @Project = '{' + CAST(NEWID() AS VARCHAR(36)) + '}'

select @Project

set @Project = REPLACE(REPLACE(REPLACE(@Project,'{',''),'}',''),'-','')

/* Cast as uniqueid if pattern matches, otherwise return null */ 
if @Project LIKE @HexPattern 
  select @ProjectID = CAST(
         SUBSTRING(@Project,1,8) + '-' + 
         SUBSTRING(@Project,9,4) + '-' + 
         SUBSTRING(@Project,13,4) + '-' + 
         SUBSTRING(@Project,17,4) + '-' + 
         SUBSTRING(@Project,21,LEN(@Project)-20)
         AS uniqueidentifier) 

select @ProjectID

edited Jul 9, 2010 at 0:42

answered Jul 8, 2010 at 23:20

8kb

11.5k7 gold badges41 silver badges53 bronze badges

3 Comments

Moose Over a year ago

This actually doesn't take into account that uniqueidentifiers can also be surrounded with {}, and they also may not even have the dashes. It's a bit more compact than my version but it fails in some situations. I would take out the dashes from your @Hexpattern, then before I did the test, strip out the dashes and curly braces.

8kb Over a year ago

Right...I was not thinking outside of SQL Server.

Moose Over a year ago

You don't actually have to put the dashes back before you do the cast either, but you've got a novel approach with the pattern.. it's much cleaner than my loop. I'm gonna give you this one.

Fosco · Accepted Answer · 2010-07-08 17:18:27Z

2

Not sure, but why not flip it around... at first glance I would simplify it like this:

select @ProjectID = 
   ISNULL((select ProjectID from Project where 
           projectcode = @Project)
     ,(cast @Project as uniqueidentifier))

If this doesn't provide enough error handling, I'm sure there's a better way to pre-check that the cast can work without using try/catch...

answered Jul 8, 2010 at 17:18

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

3 Comments

Moose Over a year ago

It doesn't solve the problem of the cast erroring out if @Project isn't really a code, but not a uniqueidentifier either. and, it puts the slow cart before the fast horse. Much quicker to check if it's a uniqueidentifier first, than to try to go look it up in the table.

Fosco Over a year ago

Like I said, I'm sure there's a better way to validate that the cast will function. It has to be all numeric, right? Also, is the query really that slow?...

Moose Over a year ago

yeah, I have several places where I'm doing this, one is with several million rows. the project one will probably only have a couple hundred.

Moose · Accepted Answer · 2010-07-08 18:25:50Z

My brute force method was to create my own ToGuid() function that verifies it can be converted to a GUID first, if not, it returns null. It may not be very fast but it does the job, and it is probably faster to convert the guid if it is one than to try to look it up in the table. EDIT: I meant to give credit to this blog, where I got the basis of my code for this function: http://jesschadwick.blogspot.com/2007/11/safe-handling-of-uniqueidentifier-in.html

CREATE FUNCTION [dbo].[ToGuid]
(
    @input NVARCHAR(MAX)
)
RETURNS uniqueidentifier
AS
BEGIN 
    DECLARE @isValidGuid BIT; 
    DECLARE @temp NVARCHAR(MAX); 
    SET @isValidGuid = 1; 
    SET @temp = UPPER(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@input, '-', ''), '{', ''), '}', '')))); 
    IF(@temp IS NOT NULL AND LEN(@temp) = 32) 
    BEGIN  
        DECLARE @index INT;  
        SET @index = 1
        WHILE (@index <= 32)  
        BEGIN   
            IF (SUBSTRING(@temp, @index, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F'))    
            BEGIN
                SET @index = @index + 1
            END
            ELSE    
            BEGIN
                SET @isValidGuid = 0
                BREAK;     
            END
        END    
    END
    ELSE
    BEGIN
        SET @isValidGuid = 0
    END  

    DECLARE @ret UNIQUEIDENTIFIER
    IF(@isValidGuid = 1) 
        set @ret = cast(@input AS UNIQUEIDENTIFIER)
    ELSE
        set @ret = NULL

    RETURN @ret

END

I'm still very interested if there is a better answer than this.

Fernando Meneses Gomes · Accepted Answer · 2013-09-05 14:55:32Z

1

Verify if @Project is a number using the ISNUMERIC function.

your code should looks like that:

declare @ProjectID uniqueidentifier

set @ProjectID = null

IF ISNUMERIC(@Project) > 0
BEGIN
    set @ProjectID = cast(@Project as uniqueidentifier)
END

if(@ProjectID is null)
BEGIN
    select  @ProjectID = ProjectID from Project where projectcode = @Project
END

return @ProjectID

answered Sep 5, 2013 at 14:55

Fernando Meneses Gomes

89711 silver badges15 bronze badges

Collectives™ on Stack Overflow

Try-Catch in User Defined Function?

6 Answers 6

1 Comment

Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related