Incorrect Syntax on Declared VarChar

Question

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?

Answer 1

0

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.

answered May 08 '12 at 05:13 PM

ThomasRushton ♦
30.1k ● 7 ● 10 ● 40

Thomas,

Awesome, thanks so much for you help. It works. Have a great day!

May 08 '12 at 05:36 PM SheldonMcc

(comments are locked)

Incorrect Syntax on Declared VarChar

Follow this question

Related Questions