用户自定义函数中的Try-Catch？

Question

我正在尝试编写一个UDF，将字符串（GUID或与该GUID关联的项目代码）翻译成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

如果将上述代码嵌入到我的存储过程中，这将很好地运行，但我想将其制作成一个函数，以便我遵循DRY原则。

当我尝试创建该函数时，我会收到以下错误：

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.

有人知道我如何避免这个错误吗？

编辑：我知道在函数中不能使用Try-Catch，我想简化问题，是否有一种转换方式，如果转换失败只返回NULL而不是错误？

- Moose

---

使用SQLCLR是一个选项吗？如果是的话，编写自己的SQLCLR函数将起作用。 - Moe Sisko

SQLCLR绝对不是一个选项。如果可以的话，会容易得多。最终我使用了我的解决方案和8kb的组合。 - Moose

Answer 1

显然你不能在UDF中使用TRY-CATCH。

根据SQL Server的错误报告页面：

Books Online在“CREATE FUNCTION（Transact-SQL）”主题中记录了这种行为：“以下语句在函数中有效：[...]除TRY...CATCH语句之外的控制流语句。[...]”

但是他们在2006年时给了未来的希望：

然而，这是一个严重的限制，应在将来的版本中删除。您应该就此发表建议，我会全力支持。

- DOK

---

嗯，是的，我知道我不能使用 Try-Catch，这就是我要求替代方案的原因。Fosco 的方法也可以工作，但如果转换失败，我不想要错误。 - Moose

Answer 2

从SQL Server 2012开始，您可以使用TRY_CAST/TRY_CONVERT函数：

如果转换成功，则返回转换为指定数据类型的值；否则返回null。

该函数可在函数中使用，但不能使用Try-Catch。请注意保留HTML标记。

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

- Lukasz Szozda

Answer 3

来自MSDN：

uniqueidentifier数据类型的列或本地变量可以通过以下方式之一初始化：

使用NEWID函数。

从字符串常量转换，格式为xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx，在此每个x都是0-9或a-f范围内的十六进制数字。

例如，6F9619FF-8B86-D011-B42D-00C04FC964FF是有效的uniqueidentifier值。

您可以使用模式匹配来验证字符串。请注意，这对于缩小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

- 8kb

---

实际上，这并没有考虑到唯一标识符也可以用{}括起来，而且它们甚至可能没有破折号。它比我的版本更紧凑，但在某些情况下会失败。我会从你的@Hexpattern中去掉破折号，然后在进行测试之前，将破折号和花括号删除。 - Moose

对啊，我没有考虑到 SQL Server 以外的东西。 - 8kb

你实际上在进行转换之前不必将破折号放回去，但是你的模式方法非常新颖..比我的循环更加简洁。我会让你赢一次。 - Moose

Answer 4

不确定，但为什么不反过来想呢...乍一看，我会这样简化它：

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

如果这样还不足以提供足够的错误处理，我相信有一种更好的方法可以在不使用try/catch的情况下预先检查强制转换是否可行...

- Fosco

---

它并没有解决如果@Project不是一个代码，但也不是唯一标识符时的转换错误问题。而且，它把慢车放在快马前面。先检查它是否是唯一标识符要快得多，而不是试图在表中查找它。 - Moose

就像我说的，我相信有更好的方法来验证转换是否有效。它必须全部是数字，对吧？另外，查询真的很慢吗？... - Fosco

是的，我有几个地方在做这个，其中一个有数百万行。项目中可能只有几百行。 - Moose

Answer 5

我的暴力方法是创建自己的ToGuid()函数，首先验证它是否可以转换为GUID，如果不能，则返回null。它可能不是非常快，但它完成了工作，如果GUID已经存在，将其转换可能比尝试在表中查找要快。编辑：我想给这篇博客以荣誉，我从这篇博客中获得了编写此函数代码的基础：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

如果有比这更好的答案，我仍然非常感兴趣。

- Moose

Answer 6

使用ISNUMERIC函数验证@Project是否为数字。

你的代码应该看起来像这样：

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

- Fernando Meneses Gomes