How to use a table variable within EXECUTE command?

Question

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 variable is of type TABLE and it should be VARCHAR, but how to play around it?

Answer 1

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.

Or declare it withing the Dynamic SQL as

EXECUTE sp_executesql N'DECLARE @MyTABLE TABLE (x int); 
                        INSERT INTO @MyTable VALUES (1);
                        SELECT * FROM @MyTable';

Answer 2

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

Answer 3

You can try the following way, that's how I did it, and for me it was the most effective.

Declare @SqlCommand NVARCHAR(MAX)
Declare @TableType as TableType

Set @SqlCommand = 'Select * From @table'

Exec sp_executesql @SqlCommand, N'@table as TableType', @table = @TableType

That way your @Table will resolve the value of the outside table you created, Of course, always create the two of the same type, any questions I'm available!