CODEWITHSUNDEEP
sqlsql-server-2008
user710502
user710502

Reputation: 11471

Having issues with checking if function already exists

I am having issues with trying to make this query work, I keep getting errors, here is the code and after that the related error

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

Error

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'.

Upvotes: 2

Views: 533

Answers (1)

Eddie Paz
Eddie Paz

Reputation: 2241

SQL Server is seeing this as one code block so "create function" is going to fail. Hence the error you get after doing what Justin suggested.

To do it this way (printing messages, and such), you have to execute the statement as you initially had it. Except you must set the text first:

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

Also, since you're passing this a statement, the single quotes must be escaped to avoid errors.

Upvotes: 6

Related Questions