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?
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';
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
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!
