sp_executesql and table output

Question

I'm writing a stored procedure in SQL Server 2005, at given point I need to execute another stored procedure. This invocation is dynamic, and so i've used sp_executesql command as usual:

DECLARE @DBName varchar(255) 
DECLARE @q varchar(max) 
DECLARE @tempTable table(myParam1 int, -- other params)

SET @DBName = 'my_db_name'
SET q = 'insert into @tempTable exec ['+@DBName+'].[dbo].[my_procedure]'
EXEC sp_executesql @q, '@tempTable table OUTPUT', @tempTable OUTPUT

SELECT * FROM @tempTable

But I get this error:

Must declare the scalar variable "@tempTable".

As you can see that variable is declared. I've read the documentation and seems that only parameters allowed are text, ntext and image. How can I have what I need?

PS: I've found many tips for 2008 and further version, any for 2005.

Conventual

Been a while since 2005, but the @tempTable's scope is limited to the current procedure, not the executeSQL procedure... – Jeffrey

it doesn't have anything to do with sp_executesql. it has to do with the scope of a table variable. next thing to think of would be to use a temp table, but I pretty sure that it will fail too because the sp_executesql runs on a different thread – Larcener

Answer 1

Resolved, thanks to all for tips:

DECLARE @DBName varchar(255) 
DECLARE @q varchar(max) 
CREATE table #tempTable(myParam1 int, -- other params)

SET @DBName = 'my_db_name'
SET @q = 'insert into #tempTable exec ['+@DBName+'].[dbo].[my_procedure]'
EXEC(@q)

SELECT * FROM #tempTable
drop table #tempTable

Conventual

This doesn't work for me on 2008!?! The #tempTable isn't updated in the local scope from EXEC(). – Kiernan

Answer 2

SQL Server 2005 allows to use INSERT INTO EXEC operation (https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sqlallproducts-allversions).

You might create a table valued variable and insert result of stored procedure into this table:

DECLARE @tempTable table(myParam1 int, myParam2 int);
DECLARE @statement nvarchar(max) = 'SELECT 1,2';
INSERT INTO @tempTable EXEC sp_executesql @statement;
SELECT * FROM @tempTable;

Result:

myParam1    myParam2
----------- -----------
1           2

or you can use any other your own stored procedure:

DECLARE @tempTable table(myParam1 int, myParam2 int);
INSERT INTO @tempTable EXEC [dbo].[my_procedure];
SELECT * FROM @tempTable;

Susannahsusanne

Answer 3

@tempTable's scope is limited to the current procedure.

You could replace the @tempTable with a global temporary table (i.e. ## table), but be very careful with the scope of that table and be sure to drop it when the procedure ends

Jeffrey