title: sp_executesql and table output
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But I get this error:</p>
  
  <blockquote>
    <p>Must declare the scalar variable "@tempTable".</p>
  </blockquote>
  
  <p>As you can see that variable is declared. I've read the <a href="http://technet.microsoft.com/it-it/library/ms188001%28v=sql.90%29.aspx" rel="noreferrer">documentation</a> and seems that only parameters allowed are text, ntext and image. How can I have what I need?</p>
  
  <p>PS: I've found many tips for 2008 and further version, any for 2005.</p>