[SOLVED] sp_executesql and table output

sp_executesql and table output

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.

Solution

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