x

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?
more ▼

asked May 08 '12 at 05:05 PM in Default

SheldonMcc gravatar image

SheldonMcc
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.
more ▼

answered May 08 '12 at 05:13 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 13 20 44

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x17

asked: May 08 '12 at 05:05 PM

Seen: 1138 times

Last Updated: May 09 '12 at 02:16 PM