SQL 如果不存在则创建 UDF（用户定义函数），如果存在则跳过它

Creating a UDF(User Define Function) if is does not exist and skipping it if it exists

提问by Asynchronous

Hi and thanks for reading this.

您好，感谢您阅读本文。

I am trying to use the IF EXISTS/IF NOT EXISTS statement to check if an Object exist. Basically I want to skip it if it is there or create it if it is not there.

我正在尝试使用 IF EXISTS/IF NOT EXISTS 语句来检查对象是否存在。基本上我想跳过它，如果它在那里，或者如果它不存在就创建它。

I have writing the code in two different ways but I get an error: Create function must be the only function in the batch. If I place GO between the statements as Illustrated below, I get another warning: Incorrect Syntax near GO.

我用两种不同的方式编写代码，但出现错误：创建函数必须是批处理中的唯一函数。如果我将 GO 放在如下所示的语句之间，我会收到另一个警告：GO 附近的语法不正确。

Where am I going wrong here?

我哪里出错了？

IF NOT EXISTS
(select * from Information_schema.Routines where SPECIFIC_SCHEMA='dbo' 
AND SPECIFIC_NAME = 'FMT_PHONE_NBR' AND Routine_Type='FUNCTION')

/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END

GO

Or this:

或这个：

IF NOT EXISTS
(SELECT name FROM sys.objects WHERE name = 'dbo.FMT_PHONE_NBR')

GO

/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END

GO

Thanks for checking this out!

感谢您检查这一点！

回答by TreyE

The easiest way to solve this is actually to delete the function if it already exists, and then re-create it:

解决这个问题最简单的方法实际上是删除已经存在的函数，然后重新创建它：

/* If we already exist, get rid of us, and fix our spelling */
IF OBJECT_ID('dbo.FMT_PHONE_NBR') IS NOT NULL
  DROP FUNCTION FMT_PHONE_NBR
GO

/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
END

GO

Note the usage of the 'object_id' function in the above. This is actually a pretty common way to check for the existence of an object, although it is subject to certain constraints.

注意上面'object_id'函数的用法。这实际上是一种非常常见的检查对象是否存在的方法，尽管它受到某些限制。

You can read more about it here: OBJECT_ID

您可以在此处阅读更多相关信息：OBJECT_ID

回答by Philip Kelley

As I've beaten my head on this brick wall for a long time, I'll toss in two more cents.

由于我已经在这堵砖墙上打了很长时间，我再投两分钱。

As pointed out, yes, it'd be nice to add it only if it isn't already there, but that just not possible in T-SQL without using dynamic SQL... and wrapping your functions, procedures, triggers, views, and maybe even more obscure objects as dynamic statements is just too darn impractical. (Don't ask me to support source code that might contain more than 4 single apostrophes in a row!)

正如所指出的，是的，只有在它不存在的情况下才添加它会很好，但是如果不使用动态 SQL，这在 T-SQL 中是不可能的......并且包装你的函数、过程、触发器、视图，也许更晦涩的对象作为动态语句太不切实际了。（不要让我支持可能连续包含 4 个以上单撇号的源代码！）

Dropping (if it exists) and (re)creating is a viable solution. Presumably, if you are rolling out new code, you would want to create the object if it was not already there, and otherwise drop the existing/old code and replace it with the new. (If you might accidentally replace "new" code with "old" code, you have a version control problem, which is a different and much harder topic.)

删除（如果存在）和（重新）创建是一个可行的解决方案。据推测，如果您要推出新代码，如果对象不存在，您会希望创建该对象，否则删除现有/旧代码并将其替换为新代码。（如果您可能不小心将“新”代码替换为“旧”代码，那么您就有了版本控制问题，这是一个不同且更难的话题。）

The real problem is losing information when you drop the old code. What information? The one I often hit is access rights: who has EXECUTEor, for some functions, SELECTrights on the object? Drop and replace, and they're gone. The answer to this, of course, is to script the access rights as part of the deployment script. However if you have a situation where different database-hosting environments have different configurations (logins, domains, groups, etc. etc.), you might be in a situation where you won't and can't know what the existing access rights are on a given instance, so if you just drop and recreate it, existing users may no longer be able to access it. (Extended properties and other bits of esoterica would similarly affected.)

真正的问题是删除旧代码时会丢失信息。什么资料？我经常碰到的一个是访问权限：谁拥有对象的权限，EXECUTE或者对于某些功能，SELECT对象的权限？丢弃并更换，它们就消失了。当然，对此的答案是将访问权限编写为部署脚本的一部分。但是，如果您遇到不同数据库托管环境具有不同配置（登录名、域、组等）的情况，您可能不会也无法知道现有的访问权限是什么在给定的实例上，因此如果您只是删除并重新创建它，现有用户可能无法再访问它。（扩展属性和其他神秘学知识也会受到类似的影响。）

The first and best fix for this is to implement robust security. Set up database roles, assign/associate appropriate permissions to the roles, then you won't have to know who's in the roles--that'd be the job of the environment administrators. (You'd still have to have something like GRANT EXECUTE on ThisProc to dbo.xxxat the end of your script, but that's not so hard.

对此的第一个也是最好的解决方法是实现强大的安全性。设置数据库角色，为角色分配/关联适当的权限，然后您就不必知道谁在角色中——这就是环境管理员的工作。（您仍然必须GRANT EXECUTE on ThisProc to dbo.xxx在脚本末尾添加类似内容，但这并不难。

If, like me, you (a) haven't been empowered to roll out a good and robust security model, and (b) are lazy and likely to not check the end of a hundreds-of-lines-long stored procedure file for access rights code, you can do something like the following. (This is set for stored procedures, but is adaptible for functions and other objects.)

如果像我一样，你 (a) 没有被授权推出一个好的和健壮的安全模型，并且 (b) 很懒惰并且可能不会检查数百行长的存储过程文件的末尾访问权限代码，您可以执行以下操作。（这是为存储过程设置的，但适用于函数和其他对象。）

-- isProcedure
-- IsScalarFunction    (Returns single value)
-- IsTableFunction     (Declared return table structure, multiple statements)
-- IsInlineFunction    (Based on single select statement)
-- IsView

IF objectproperty(object_id('dbo.xxx'), 'isProcedure') is null
 BEGIN
    --  Procedure (or function) does not exist, create a dummy placeholder
    DECLARE @Placeholder varchar(100)
    SET @Placeholder = 'CREATE PROCEDURE dbo.xxx AS RETURN 0'
    EXEC(@PlaceHolder)

    --  Configure access rights
    GRANT EXECUTE on dbo.xxx TO StoredProcedureUser
 END
GO

ALTER PROCEDURE dbo.xxx
(etc.)
GO

This will:

这会：

• First check if the procedure exists. If it doesn't, create a "placholder", and set up the appropriate access rights to it
• Then, whether or not it existed before the script was run, ALTERand set it with the desired code.

• 首先检查程序是否存在。如果没有，请创建一个“占位符”，并为其设置适当的访问权限
• 然后，在脚本运行之前它是否存在，ALTER并将其设置为所需的代码。

There's also the problem of managing code-based objects (primarily stored procedures) in schemas where the schemas might not exist. I've yet to figure that one out, and if you're lucky, you'll never end up in a similarly oddball situation.

在模式可能不存在的模式中管理基于代码的对象（主要是存储过程）也存在问题。我还没有弄清楚这一点，如果你很幸运，你永远不会陷入类似古怪的境地。

回答by Anthony Griggs

Actually this works in 2008

实际上这在 2008 年有效

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetTZDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    execute dbo.sp_executesql @statement = N'
        CREATE FUNCTION [dbo].[fn_GetTZDate] ()

        RETURNS datetime
        AS -- WITH ENCRYPTION AS
        BEGIN
            -- Declare the return variable here
            DECLARE @tzadj int, @sysdate datetime
            SET @sysdate = getdate()
            SET @tzadj = 0
            SELECT @tzadj = [tzAdjustment] FROM USysSecurity WHERE [WindowsUserName] = SYSTEM_USER
            if @tzadj <> 0
            BEGIN
                SET @sysdate = dateadd(hh, @tzadj, @sysdate)
            END

            -- Return the result of the function
            RETURN @sysdate

        END    ' 
END

GO

回答by Stefan Steiger

Necromancing.
Dropping isn't a good idea, as there might be permissions set on an object.

死灵法术。
删除不是一个好主意，因为可能在对象上设置了权限。

Therefore, the proper way to do it would actually be to
A) Create the function if it doesn't exist (dummy)
B) ALTER the function if it already exists. (it might not be up-to-date)

因此，正确的做法实际上是
A) 如果该函数不存在（虚拟），则创建该函数
B) 如果该函数已经存在，则更改该函数。（它可能不是最新的）

Example:

例子：

-- DROP FUNCTION IF EXISTS [dbo].[TestFunction]

-- Do not drop the function if it exists - there might be privileges granted on it... 
-- You cannot alter function from table-valued function to scalar function or vice-versa 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFunction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) 
BEGIN
    -- CREATE FUNCTION dbo.[TestFunction]() RETURNS int AS BEGIN RETURN 123 END 
    -- CREATE FUNCTION dbo.[TestFunction]() RETURNS table AS RETURN (SELECT * FROM information_schema.tables)  
    EXECUTE('
        CREATE FUNCTION dbo.[TestFunction]() RETURNS int AS BEGIN RETURN 123 END 
    ')
END 
GO



-- ALTER FUNCTION dbo.[TestFunction](@abc int) RETURNS table AS RETURN (SELECT * FROM information_schema.tables)  
ALTER FUNCTION dbo.[TestFunction]() RETURNS int AS BEGIN RETURN 'test' END 

Note that you can't change a table-valued function into a scalar function or vice-versa.
You can however change the arguments types and number of arguments - as well as the return schema - at will.

请注意，您不能将表值函数更改为标量函数，反之亦然。
但是，您可以随意更改参数类型和参数数量 - 以及返回模式。

回答by Mike Monteiro

The error message is exactly right, that CREATE FUNCTION statements must the first in a batch, which means that unfortunately you can't do:

错误消息是完全正确的， CREATE FUNCTION 语句必须是批处理中的第一个，这意味着不幸的是你不能这样做：

IF [condition]
BEGIN
    CREATE FUNCTION
    ...
END
GO

What I usually do in this situation is:

在这种情况下，我通常会做的是：

IF object_id('dbo.myFunction') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.myFunction
END
GO

CREATE FUNCTION dbo.myFunction (
    ...
)
GO

Note that I usually use the object_id() function as it's simpler, easier to read, and more robust than EXISTS (SELECT * FROM sys.whatever).

请注意，我通常使用 object_id() 函数，因为它比 EXISTS (SELECT * FROM sys.whatever) 更简单、更易于阅读且更健壮。

Of course, this solution will only work for you if you're OK with always overwriting any previous definition of the function. If that isn't OK in your situation, let me know.

当然，此解决方案仅适用于您可以始终覆盖函数的任何先前定义的情况。如果您的情况不合适，请告诉我。

回答by Dinosaure

TL;DR

TL; 博士

Here is the op's use-case (dbo.sp_insertoralterprocedure is given as a factory just after Below):

这是操作的用例（dbo.sp_insertoralter程序在下面作为工厂给出）：

EXEC dbo.sp_insertoralter '[dbo].[FMT_PHONE_NBR]', '(@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 4, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 7, 4)
END', 0, 'IsScalarFunction'

print [dbo].[FMT_PHONE_NBR] ('987654132456') --  987-654-1324

Warning: Please do keep in mind that it's not intended to be a general-purpose factory, as user rights may not apply same everywhere, and there are tons of possible sql injections.

警告：请记住，它不是一个通用工厂，因为用户权限可能并不适用于所有地方，并且有大量可能的 sql 注入。

---

---

Original answer

原答案

Starting from @PhilipKelley's answer, if you wish to use a schema other than 'dbo', see Below. No temp var are required to make EXEC('sql code')instructions working by themselves.

从@PhilipKelley 的回答开始，如果您希望使用“dbo”以外的模式，请参见下文。不需要临时变量来使EXEC('sql code')指令自行工作。

For the sake of the @obj_lookupreuse, you may uncomment the last EXEC('PRINT...')part.

为了@obj_lookup重用，您可以取消对最后EXEC('PRINT...')一部分的注释。

Important note: Everything that is stringified will not have spellcheck enabled. It's up to you to be sure that names (schema and function) are accurate, and any db rework might break names/schemas without spellcheck notice.

重要说明：字符串化的所有内容都不会启用拼写检查。由您来确保名称（架构和函数）准确无误，并且任何数据库返工都可能在没有拼写检查通知的情况下破坏名称/架构。

I strongly recommend to read the full @PhilipKelley's answer, especially for the "Don't ask me to support source code that might contain more than 4 single apostrophes in a row!"part.

我强烈建议阅读完整的@PhilipKelley 的回答，特别是对于“不要让我支持可能连续包含 4 个以上单撇号的源代码！” 部分。

Reminder: This is not a concurrent-safe operation, as parallel executions might fail not being able to create the same object (concurrently in creation), and/or will result in an unknown object's altered body (presumably the last one to succeed). As are all the other "drop-then-create" answers, but the warn might not harm.

提醒：这不是并发安全的操作，因为并行执行可能会失败，无法创建相同的对象（同时在创建中），和/或会导致未知对象的主体改变（大概是最后一个成功）。与所有其他“删除然后创建”的答案一样，但警告可能不会造成伤害。

Below:

以下：

-- IsProcedure
-- IsScalarFunction    (Returns single value)
-- IsTableFunction     (Declared return table structure, multiple statements)
-- IsInlineFunction    (Based on single select statement)
-- IsView
-- (Full list available at https://docs.microsoft.com/fr-fr/sql/t-sql/functions/objectproperty-transact-sql?view=sql-server-ver15 )

DECLARE @obj_lookup nvarchar (max) = 'abc.xxx'
DECLARE @obj_alter nvarchar (max) = '() --  Object parameters come here
    RETURNS INT AS BEGIN --  Actual body goes here
        RETURN 0
    END'
DECLARE @obj_oralter bit = 0
DECLARE @obj_type nvarchar(100) = 'IsScalarFunction'
DECLARE @obj_application nvarchar(100) = 'FUNCTION'

IF objectproperty(object_id(@obj_lookup), @obj_type) is null
 --  Here is the "create if not exists" behaviour
 BEGIN
    EXEC('CREATE FUNCTION ' + @obj_lookup + @obj_alter)
    --  Configure access rights
    EXEC('GRANT EXECUTE on ' + @obj_lookup + ' TO StoredProcedureUser')
 END
ELSE IF @obj_oralter = 1 --  Here is the "or alter" behaviour
    EXEC('ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter) --  Untouched access rights
/* --  Alternatively, you may prefer this 'CREATE OR ALTER' instruction to avoid the above objectproperty [if/else if] block:
EXEC('CREATE OR ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter)
*/
GO

-- Actual code (considering the object now exists or was altered)
PRINT abc.xxx()
(..etc)
/* -- For the "@obj_lookup" reuse:
EXEC('PRINT ' + @obj_lookup + '()
(..etc)')
*/
GO

---

---

Factory

工厂

That can be wrapped in a procedure (yay):

这可以包含在一个过程中（是的）：

CREATE PROCEDURE dbo.sp_insertoralter(@obj_lookup as nvarchar(max), @obj_alter as nvarchar(max), @obj_oralter as bit = 0, @obj_type as nvarchar(100))
 AS BEGIN
    --  Type preparation
    declare @obj_application nvarchar(100) = case
            when @obj_type = 'IsProcedure' then 'PROCEDURE'
            when @obj_type = 'IsScalarFunction' or @obj_type = 'IsTableFunction' or @obj_type = 'IsInlineFunction' then 'FUNCTION'
            when @obj_type = 'IsView' then 'VIEW'
            when @obj_type = 'IsTable' or @obj_type = 'IsUserTable' then 'TABLE'
            when @obj_type = 'IsTrigger' then 'TRIGGER'
            else null --  Restriction to known (usefull) cases
        end
    if @obj_application is null
    begin
        raiserror ('An invalid @obj_type was specified for procedure', 10, 1); --throw 51000, 'An invalid @obj_type was specified for procedure', 1;
        return
    end

    IF objectproperty(object_id(@obj_lookup), @obj_type) is null
     BEGIN --  Here is the "create if not exists" behaviour
        EXEC('CREATE ' + @obj_application + ' ' + @obj_lookup + @obj_alter)
        --  Configure access rights
        EXEC('GRANT EXECUTE on ' + @obj_lookup + ' TO StoredProcedureUser')
     END
    ELSE IF @obj_oralter = 1 --  Here is the "or alter" behaviour
        EXEC('ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter) --  Untouched access rights
    /* --  Alternatively, you may prefer the 'CREATE OR ALTER' instruction to avoid the above objectproperty [if/else if] block:
    EXEC('CREATE OR ALTER ' + @obj_application + ' ' + @obj_lookup + @obj_alter)
    */
 END
GO

As you might have noticed, that procedure can then be self-invoked (but has to exist to be invoked), so here is an auto-use-case (double yay):

正如您可能已经注意到的那样，该过程可以被自调用（但必须存在才能被调用），所以这是一个自动用例（双重耶）：

--  The "dbo.sp_insertoralter" procedure has to exist (or whatever name you gave it), let's pretend someone manually executed the `Below:` part with these parameters:
EXEC dbo.sp_insertoralter 'dbo.sp_insertoralter', '(@obj_lookup as nvarchar(max), @obj_alter as nvarchar(max), @obj_oralter as bit = 0, @obj_type as nvarchar(100))
 AS BEGIN
    --  Type preparation
    declare @obj_application nvarchar(100) = case
            when @obj_type = ''IsProcedure'' then ''PROCEDURE''
            when @obj_type = ''IsScalarFunction'' or @obj_type = ''IsTableFunction'' or @obj_type = ''IsInlineFunction'' then ''FUNCTION''
            when @obj_type = ''IsView'' then ''VIEW''
            when @obj_type = ''IsTable'' or @obj_type = ''IsUserTable'' then ''TABLE''
            when @obj_type = ''IsTrigger'' then ''TRIGGER''
            else null --  Restriction to known (usefull) cases
        end
    if @obj_application is null
    begin
        raiserror (''An invalid @obj_type was specified for procedure'', 10, 1); --throw 51000, ''An invalid @obj_type was specified for procedure'', 1;
        return
    end

    IF objectproperty(object_id(@obj_lookup), @obj_type) is null
     BEGIN --  Here is the "create if not exists" behaviour
        EXEC(''CREATE '' + @obj_application + '' '' + @obj_lookup + @obj_alter)
        --  Configure access rights
        EXEC(''GRANT EXECUTE on '' + @obj_lookup + '' TO StoredProcedureUser'')
     END
    ELSE IF @obj_oralter = 1 --  Here is the "or alter" behaviour
        EXEC(''ALTER '' + @obj_application + '' '' + @obj_lookup + @obj_alter) --  Untouched access rights
    /* --  Alternatively, you may prefer the ''CREATE OR ALTER'' instruction to avoid the above objectproperty [if/else if] block:
    EXEC(''CREATE OR ALTER '' + @obj_application + '' '' + @obj_lookup + @obj_alter)
    */
 END', 1, 'IsProcedure'

And now, here is the op's use-case (triple yay):

现在，这是操作的用例（三重耶）：

EXEC dbo.sp_insertoralter '[dbo].[FMT_PHONE_NBR]', '(@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 4, 3) + ''-'' + 
           SUBSTRING(@phoneNumber, 7, 4)
END', 0, 'IsScalarFunction'

print [dbo].[FMT_PHONE_NBR] ('987654132456') --  987-654-1324

Warning: Please do keep in mind that it's not intended to be a general-purpose factory, as user rights may not apply same everywhere, and there are tons of possible sql injections.

警告：请记住，它不是一个通用工厂，因为用户权限可能并不适用于所有地方，并且有大量可能的 sql 注入。