title: Cannot get results of a stored procedure into a #TempTable to work
body: <p>I am using SQL Server 2008 R2 and am trying to get the results of a stored procedure into a temporary table that I can access later on in the calling stored proc. My TSQL is as follows:</p>
  
  <pre><code>CREATE PROCEDURE sp_ToBeCalled AS
  (
     @SomeParam INT
  )
  BEGIN
      SELECT * FROM tblSomeTable WHERE SomeField = @SomeParam 
  END
  
  
  CREATE PROCEDURE sp_CallingProcedure AS
  (
     @SomeOtherParam INT
  )
  BEGIN
      -- A
      SELECT * INTO #MyTempTable FROM sp_ToBeCalled(@SomeOtherParam)
  
      -- B
      SELECT * FROM #MyTempTable FOR XML RAW
  END
  </code></pre>
  
  <p>This all compiles fine however when I call sp_CallingProcedure statement -- B returns an error that #MyTempTable. </p>
  
  <p>How can I do "A" so that I can access its results from within a #MyTempTable table without having to declare the structure of #MyTempTable first?</p>
  
  <p>I am looking for a solution that I can use generically. I have a number of existing stored procedures that I need to call from various callers where getting the results queryable is a necessity. I cannot change the existing stored procedures. </p>
  
  <p>I don't want to use</p>
  
  <ol>
  <li><code>OPENQUOERY()</code> - requires a custom linked server definition </li>
  <li><code>sp_ExecSql()</code> - means I have to build up dynamic SQL which does not give me SP compile time checking.</li>
  </ol>