问题背景 #

编写数据库升级脚本时，需要通过程序集添加一个Regex正则表达式CLR用户定义函数，且脚本需支持重复执行，因此需要先检查函数是否存在，再执行创建逻辑。

初始尝试与报错 #

初始写法如下：

IF NOT EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Regex'
                    AND Routine_Type = 'FUNCTION' ) 
BEGIN
    CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000)) RETURNS [nvarchar](4000) AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]
END
GO

执行时触发语法错误：

Incorrect Syntax: 'CREATE FUNCTION' must be the only statement in the batch

若在CREATE FUNCTION前后添加GO拆分批处理，会破坏BEGIN/END的逻辑结构，导致新错误：

Could not find stored procedure

第二种尝试的问题 #

尝试先删除已存在的函数再重建：

IF EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Regex'
                    AND Routine_Type = 'FUNCTION' ) 
BEGIN
    DROP FUNCTION [Regex]
END
GO
CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000)) RETURNS [nvarchar](4000) AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]
GO

但由于该函数被其他约束引用，重复执行时会报错：

Msg 3729, Level 16, State 1, Line 41

Cannot DROP FUNCTION 'Regex' because it is being referenced by object 'RX_T_DataManagementItems_ItemIdent'.

Msg 2714, Level 16, State 51, Procedure regex, Line 1 [Batch Start Line 43]

There is already an object named 'regex' in the database.

解决方案 #

使用动态SQL可以完美解决这个问题——动态SQL会作为独立批处理执行，既满足CREATE FUNCTION必须单独成批的语法要求，又能保留条件判断逻辑：

IF NOT EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Regex'
                    AND Routine_Type = 'FUNCTION' ) 
BEGIN
    EXEC sp_executesql N'
        CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000)) 
        RETURNS [nvarchar](4000) 
        AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]
    '
END
GO

补充优化写法 #

也可以通过sys.objects直接检查函数存在性，写法更简洁且覆盖所有函数类型（包括CLR函数）：

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[regex]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXEC sp_executesql N'
        CREATE FUNCTION [dbo].[regex] (@text [nvarchar](4000), @regex [nvarchar](4000)) 
        RETURNS [nvarchar](4000) 
        AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]
    '
END
GO

这种方案的优势：

• 仅在函数不存在时执行创建逻辑，避免重复创建报错
• 无需删除函数，规避了依赖约束的删除失败问题
• 动态SQL内部的CREATE FUNCTION符合独立批处理的语法要求

内容的提问来源于stack exchange，提问作者MaxH