CODEWITHSUNDEEP
sqlsql-servert-sql
Joel
Joel

Reputation: 438

TSQL: Variable scope and EXEC()

declare @test varchar(20)
set @test = 'VALUE'

exec('
select '+@test+'
')

This returns:

Invalid column name 'VALUE'.

Is there an alternate method to display the variable value on the select statement?

Upvotes: 1

Views: 5043

Answers (3)

Tomalak
Tomalak

Reputation: 338118

The clean alternative is to use sp_executesql() and its ability to deal with parameterized statements:

DECLARE @test VARCHAR(20)
DECLARE @stmt NVARCHAR(100)
DECLARE @parm NVARCHAR(100)

SET @stmt = N'SELECT @val'        /* statement definition */
SET @parm = N'@val VARCHAR(20)'   /* parameters definition */
SET @test = 'VALUE'

EXECUTE sp_executesql @stmt, @parm, @val = @test

Yields:

(no column name)
VALUE

Upvotes: 8

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

cmsjr has a point about not needing exec for this. But assuming you're posting a simplified version of the problem and exec is a requirement:

There's no column named "value" because there's no table. If you just want it to print value, you need to encase it in quotes so it looks like a string literal inside the call to exec:

exec(' select ''' + @test + ''' ')

Upvotes: 3

cmsjr
cmsjr

Reputation: 59145

You don't need to use Exec in this context If you are doing a direct assignment you can use 

Set @test = 'VALUE'

and to display the value just select it without assigning a value

Select @test

Upvotes: 3

Related Questions