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, 2012 at 05:05 PM in Default

avatar image

SheldonMcc
0 1 1 2

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

1 answer: sort voted first

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, 2012 at 05:13 PM

avatar image

ThomasRushton ♦♦
39.9k 20 49 52

Thomas,

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

May 08, 2012 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.

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:

x2077
x31

asked: May 08, 2012 at 05:05 PM

Seen: 1773 times

Last Updated: May 09, 2012 at 02:16 PM

Copyright 2016 Redgate Software. Privacy Policy