Solved

TSQL syntax error msg 102

Posted on 2013-01-26
9
514 Views
Last Modified: 2013-01-27
I'm getting a MSG 102 error for the script below.  I think it is something to do with the syntax around the end of the @SQL variable, in bold and underlined.  I've tried braces and quotes but now getting frustrated....!

Begin

      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
      
END
0
Comment
Question by:TMS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 38822417
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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38822455
Without seeing the value of @t, and without seeing the requested output, not easy to tell.
0
 

Author Comment

by:TMS
ID: 38822469
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:TMS
ID: 38822509
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38822621
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

Open in new window


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

Expert Comment

by:Qlemo
ID: 38823086
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

Open 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.
0
 

Author Comment

by:TMS
ID: 38823685
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38823747
I wonder how you can get a CASE error on my suggestion, as I don't have any CASE at all ...
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

print @sql
EXEC sp_executesql @SQL
end

Open in new window

can you post the result of @sql?
0
 

Author Comment

by:TMS
ID: 38824232
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.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question