TSQL: Variable scope and EXEC()

Question

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?

Answer 1

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

Answer 2

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 + ''' ')

Answer 3

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