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.
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