question

SheldonMcc avatar image
SheldonMcc asked

Incorrect Syntax on Declared VarChar

declare @sql varchar(max) declare @colnm varchar(max) declare @PDSLINEID varchar(max) set @sql = '' set @colnm = 'CHKISODATA11' set @PDSLINEID = '2500999YN8888' select @sql = 'SELECT ' + @colnm + ' FROM PDTABLE_FABISOS WHERE CHKISODATA15 = ' + @PDSLINEID + '' EXEC (@sql) results in an error from SQL Server 2008: ...incorrect syntax near 'YN8888'... Why would SQL do this when it is declared as a varchar?
sql-server-2008syntax
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
The "incorrect syntax near 'YN8888'" error is generated by the `EXEC (@sql)` line. It is not due to the `SET @PDSLINEID...` line, but to the `SELECT...` line immediately following it. If you change that line as follows, all should be good: SELECT @sql = 'SELECT ' + @colnm + ' FROM PDTABLE_FABISOS WHERE CHKISODATA15 = ''' + @PDSLINEID + '''' Here we are wrapping the `@PDSLINEID` value in quotes, as it is a string. You can help keep an eye out for these things when developing your code by putting in `SELECT @sql` before you try to execute the command - that'll show you if you're about to run the right thing or not.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SheldonMcc avatar image SheldonMcc commented ·
Thomas, Awesome, thanks so much for you help. It works. Have a great day!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.