SQL stmt in a var

Question

SQL stmt in a var

sujosh

Ten Centuries

Points: 1366
More actions
January 22, 2002 at 1:58 pm

#77932

I want to have the following line set in a variable @S.
select @table ='abc'
like
select @S = 'select @table ='abc''
When I do the above I get an error incorrect syntax near abc
Please help
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

In SQL, since we use single quotes to mark the beginning and end of character strings, we have to double up the single quotes when we intend for single quotes to be part of the SQL string. For instance:

SET @S = 'SELECT @table = ''abc'' '

This should set @S equal to:

SELECT @table = 'abc'

K. Brian Kelley

bkelley@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/bkelley/

Edited by - bkelley on 01/22/2002 2:22:58 PM

K. Brian Kelley
@kbriankelley

sujosh Ten Centuries Points: 1366 More actions · Answer 2

Thanks Brian! So is two single quote equal 1 single quote. Is that why we need

SET @S = 'SELECT @table = ''abc'' '

Thanks again

sujosh Ten Centuries Points: 1366 More actions · Answer 3

Also got another quick question about the same thing. If I want to print the @col which is a variable itself how can I get it?

I have @col dynamically assigned from syscolumns as say abc. Now the next stmmt I want it to be assigned to another var so I can use it for another purpose.

@col = 'abc'

select @S = 'select @col_name = ''+ @col+'' '

print @S

When I do this all I get is

select @col_name = '+ @col+' and not

select @col_name = 'abc'

Thanks

sujosh Ten Centuries Points: 1366 More actions · Answer 4

Thanks I got it

select @S = 'select @col_name = '''+@col+''''

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 5

Yes, it looks like you've got the hang of it!

K. Brian Kelley

bkelley@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley