TSQL syntax error msg 102

What is the purpose of it? I suppose adding a ";" to the SQL is the culprit. Probably you want to append a varchar value to csmerge, with a semicolon as separator?

      SET CONCAT_NULL_YIELDS_NULL OFF
      SET @i = @i + 1
      SET @SQL = 'UPDATE dbo.SFSOURCE SET csmerge = csmerge + CASE When'+' ' + @t + CAST(@i as VARCHAR(2))+' ' + 'IS null then null else' + ' '';'' ' + @t + CAST(@i as VARCHAR(2)) +' '+ 'END'
      EXEC sp_executesql @SQL

Select allOpen in new window

Without seeing the value of @t, and without seeing the requested output, not easy to tell.

Yes, I have 20 fields to concatenate so I add the first two and then add the next to the new combined and loop to the end.  The values are separated with a semi colon.  I took out the semi colon and get the same error which is near the last CAST

I've just tried to use a variable for the semi colon but I get the same result.  The output is given by the below:  @t is just the alpha part of the column name.  The script works if run on its own but not as a procedure.

DECLARE @i INT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @t VARCHAR(2)
DECLARE @tbl NVARCHAR(MAX)
DECLARE @s varchar

/*Update dbo.sfsource SET csmerge = CS1*/
SET @i = 0
SET @t = 'CS'
SET @s = ';'


While @i < 20
Begin

this should do:

Begin
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'UPDATE dbo.SFSOURCE SET csmerge = csmerge '

DECLARE @i INT
SET @i = 0
While @i < 20
Begin 
      SET @i = @i + 1
      SET @SQL = @SQL + ' + ISNULL( CS'+ CAST(@i as VARCHAR(2))+' , ''';''') '
end

EXEC sp_executesql @SQL
end

Select allOpen in new window

watch out, the ''';'''  has indeed 2 times 3 single quotes, no double quotes.

Don't think that is correct, a3. If the column is null, nothing should get appended, else a semi-colon and the column value.

      SET CONCAT_NULL_YIELDS_NULL OFF
      SET @i = @i + 1
      SET @SQL = 'UPDATE dbo.SFSOURCE SET csmerge = csmerge + CASE When '
       + @t + CAST(@i as VARCHAR(2))
       + ' IS null then null else '';'' + '
       + @t + CAST(@i as VARCHAR(2))
       + ' END'
      EXEC sp_executesql @SQL

Select allOpen in new window

Note that the semi-colon is enclosed in two ticks. In my first comment I forgot to include an + sign in the string itself.

Angel, thanks that sorted the semi colon out but I now get a msg 156 on CASE.
Qlemo, yes the SQL is:

Update dbo.sfsource
SET csmerge = csmerge + CASE When cs20 IS null then null else ';'+CS20 END.  

The result I want is if a field is null then it won't include in the concatenation but if not then to include with the semicolon prefixed tothe field value.  Hence the result may look like: value(cs1);value(cs2);valure(cs7)........  
It's not my preferred design but that of a Salesforce config that I have been handed.
The above SQL does runs in explicit form.

Sorry, clearly not concentrating.
Yes, that now works in principle.  Need to tweek to get rid of the blank spaces and semicolons but the function builds the SQL and works.
Thanks guys for your help.  Will try and find out why the CASE statement doesn't work.