Try-Catch in User Defined Function?

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?

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.

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

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

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...

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.

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