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

问题描述：

您好，感谢您阅读本文.

Hi and thanks for reading this.

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

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.

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

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.

我哪里出错了?

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

或者这个:

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

感谢您查看此内容！

答

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

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

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

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

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

相关推荐