CODEWITHSUNDEEP
t-sqlsql-server-2008
Mité
Mité

Reputation: 1118

Incorrect syntax in IF body

I try to create this function which concatenates all rows into one string:

CREATE function [dbo].[fn_GetProjectDevelopers] ( 
  @ProjectId         varchar(100)
)
returns varchar
as
begin

DECLARE @DevList varchar

DECLARE @DevName varchar(50)
SET @DevList = '';
DECLARE my_cursor CURSOR FOR
SELECT DISTINCT ServiceAppointment.OwnerIdName as Name 
                    FROM ServiceAppointment

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @DevName 

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @DevName NOT LIKE '%' + @DevList +  '%'
        BEGIN
            @DevList = @DevName+ ' ' + @DevList
        END
    FETCH NEXT FROM my_cursor INTO @DevName
END

CLOSE my_cursor
DEALLOCATE my_cursor


  return @DevList
end

I get these errors:

Incorrect syntax near '@DevList'.
Incorrect syntax near the keyword 'CLOSE'.
Incorrect syntax near 'end'.

It's the @DevList in the BEGIN ... END part in the IF body.

Upvotes: 1

Views: 380

Answers (3)

SQLMenace
SQLMenace

Reputation: 134961

The error is this

 @DevList = @DevName+ ' ' + @DevList

should be this

SET  @DevList = @DevName+ ' ' + @DevList

Your other problem is

DECLARE @DevList varchar

Where is the size? that will only be 1 character!!

however there are better ways to do this

Here is a way without a cursor

DECLARE @DevList varchar(8000)
SET @DevList = '';

SELECT   @DevList = @DevList +  OwnerIdName + ' '
from (select distinct OwnerIdName 
                    FROM ServiceAppointment where OwnerIdName  is not null ) x 

SELECT @DevList

Runnable example

DECLARE @DevList varchar(8000)
SET @DevList = '';

SELECT   @DevList = @DevList +  name + ' '
from (select distinct name  FROM sysobjects) x 

SELECT @DevList

Upvotes: 1

triplestones
triplestones

Reputation: 393

You're missing the SET from the IF statement :)

BEGIN
IF @DevName NOT LIKE '%' + @DevList +  '%'
    BEGIN
        SET @DevList = @DevName+ ' ' + @DevList
    END
FETCH NEXT FROM my_cursor INTO @DevName
END

Upvotes: 1

rosscj2533
rosscj2533

Reputation: 9323

You are missing the SET keyword:

SET @DevList = @DevName+ ' ' + @DevList

Upvotes: 0

Related Questions