sql - 在检查功能是否已经存在时遇到问题

Question

我在尝试使这个查询工作时遇到问题，我不断收到错误，这是代码，之后是相关错误

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConcatNames]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN

   EXECUTE dbo.sp_executesql @statement = N'
      create function dbo.ConcatNames(@ProdId int) returns varchar(8000)
      as
      begin 
      declare @output varchar(8000)
      select @output = coalesce(@output + ', ', '') + Name
      from Reports
      where ProdID = @ProdId and Name > ''
      return @output
      end'
     PRINT N'Created function ConcatNames'
  END
  ELSE
  BEGIN
    PRINT N'function ConcatAttributeNames Already Exists'
  END

错误

Msg 119, Level 15, State 1, Line 8
Must pass parameter number 2 and subsequent parameters as '@name = value'.
After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Answer 1

SQL Server 将此视为一个代码块，因此“创建函数”将失败。因此，在按照贾斯汀的建议进行操作后会出现错误。

要以这种方式执行(打印消息等)，您必须按最初的方式执行语句。除了你必须先设置文本:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConcatAttributeNames]') AND
 type in (N'FN', N'IF', N'TF', N'FS', N'FT')) 
BEGIN 

    declare @statement nvarchar(4000)

    set @statement = N'
    create function [dbo].[ConcatAttributeNames] (@prodId int) returns varchar(8000) 
    as 
    begin  
        declare @output varchar(8000)

        select @output = coalesce(@output + '', '', '''') + AttributeName 
        from Report_Attribute 
        where ProdID = @ProdId and AttributeName > '''' 

        return @output 
    end '

    exec sp_executesql @statement

    PRINT N'Created function ConcatAttributeNames' 
END 
ELSE 
BEGIN 
    PRINT N'function ConcatAttributeNames Already Exists' 
END 

此外，由于您要传递此语句，因此必须对单引号进行转义以避免错误。