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

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!

感谢您查看此内容！

4 个解决方案

#1

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

#2

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 EXECUTE or, for some functions, SELECT rights 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权限？放下并更换，它们就消失了。当然，答案是将访问权限编写为部署脚本的一部分。但是，如果您遇到不同数据库托管环境具有不同配置（登录，域，组等）的情况，您可能处于这样的情况：您不会也无法知道现有的访问权限是什么在给定的实例上，如果您只是删除并重新创建它，现有用户可能无法再访问它。（扩展属性和esoterica的其他部分同样会受到影响。）

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.xxx at the end of your script, but that's not so hard.

对此的第一个也是最好的解决方案是实现强大的安全性。设置数据库角色，为角色分配/关联适当的权限，然后您不必知道角色中的谁 - 这将是环境管理员的工作。（在剧本结束时，你仍然需要在ThisProc上使用GRANT EXECUTE到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
首先检查程序是否存在。如果没有，请创建“placholder”，并为其设置适当的访问权限
Then, whether or not it existed before the script was run, ALTER and 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.

还存在在模式中管理基于代码的对象（主要是存储过程）的问题，其中模式可能不存在。我还没有想出那一个，如果你很幸运，你将永远不会陷入同样的怪异局面。

#3

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.

当然，如果你总是可以覆盖任何以前的函数定义，那么这个解决方案只对你有用。如果你的情况不好，请告诉我。

#4

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

#1