SQL SERVER:how to use table variable within EXECUTE command?

2821 views sql
3

I have this code:

declare @mytable TABLE(x int);
EXECUTE ('SELECT * from '+@mytable );

When I run the code, I get this error:

Must declare the scalar variable "@mytable ".

I know that @mytable is of type table and it should be varchar, but how to play around it?

answered question

2 Answers

7

You can put the whole thing in quotes:

EXECUTE( '
DECLARE @mytable TABLE(x int);
SELECT * from @mytable' )

Another option is to roll it into various variables, concat them, and then run the query:

DECLARE @sql_declarations NVARCHAR(1000)
DECLARE @sql_query NVARCHAR(1000)
DECLARE @sql_final NVARCHAR(4000)

SET @sql_declarations = N'declare @mytable TABLE(x int);'
SET @sql_query = N'SELECT * from @mytable;'
SET @sql_final = @sql_declarations + @sql_query

exec sp_executesql @sql_final

posted this
10

Simply, you can't.

This is not possible, cause the table variable is isolated from the scope of the Dynamic SQL. You can only use temporary and normal tables.

posted this

Have an answer?

JD

Please login first before posting an answer.