sp_executesql

Question

sp_executesql

deepforest

SSCommitted

Points: 1780
More actions
January 9, 2006 at 2:27 pm

#96263

Anyone knows why this is throwing an error ?
declare @i char(1)
set @i='a'
exec sp_executesql 'select *,'+@i+' from some_tbl'
thanx

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

You must build you string before hand as concatenation characters are not allowed. From Books Online (highlight mine):

Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is legal, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory.

The following works:

declare @i char(1)
declare @sql nvarchar(4000)
set @i='a'
SET @sql = 'select *,'+@i+' from some_tbl'
exec sp_executesql @sql

K. Brian Kelley
@kbriankelley

deepforest SSCommitted Points: 1780 More actions · Answer 2

I know, but that's not it, it's complaining about the value of

the variable as an unknown column.

It needs quotes or smthng.

Invalid column name 'a'.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Does the table you're querying have a column named a? Since you have SELECT *, is the a column even necessary?

K. Brian Kelley
@kbriankelley

deepforest SSCommitted Points: 1780 More actions · Answer 4

Got it, this works:

declare @i char(1)
declare @sql nvarchar(4000)
set @i='a'
SET @sql = 'select *,''''+@i+'''' from some_tbl'
exec sp_executesql @sql

@i is a value fetched from a different table by cursor

thnx anyway

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 5

Huh? You should get an error stating that @i does not exist in the batch context...

Try:

Declare @sql nVarChar(4000)

Set @sql=N'Declare @i Char(1) Set @i=''a'' Select *,@i from dbo.sysindexes'

Exec sp_ExecuteSQL @sql

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

He wants not to return a column, but a value. We'd do something like this normally:

SELECT column, 'static value'
FROM table

To get the same result when you're building a string, you'll need to double up on the single quotes. Like so:

SET @sql = 'SELECT column, ''static value'' FROM table'

Except his is a little more convoluted in that because he wants to put in the static value froma variable.

K. Brian Kelley
@kbriankelley

deepforest SSCommitted Points: 1780 More actions · Answer 7

deepforest

SSCommitted

Points: 1780

January 10, 2006 at 9:20 am

#613850

Exactly, Brian.

Vladan SSC-Insane Points: 22072 More actions · Answer 8

Or, if you wish to avoid multiple single quotes, you can make single quotes part of the string (expanding the @i to 3 character length at the same time):

declare @i char(3)

set @i= CHAR(39) + 'a' + CHAR(39)

exec sp_executesql 'select *,'+@i+' from some_tbl'

deepforest SSCommitted Points: 1780 More actions · Answer 9

deepforest

SSCommitted

Points: 1780

January 11, 2006 at 7:22 am

#614057

That's cool too, thanx