title: Filling Temporary Table From Stored Procedure Using Dynamic SQL
body: <p>I am trying to populate a predefined temporary table with the results of a stored procedure that executed using dynamic SQL. This temporary table is inside of another stored procedure and is returned as part of the result set once it is filled. The code to create the table and insert the data is as follows:</p>
  
  <pre><code>CREATE TABLE #MyTable
  (
      As_Of_Date DATE NULL,
      Fund VARCHAR(50) NULL,
      Code VARCHAR(100) NULL,
      Amount FLOAT NULL
  );
  
  DECLARE @SQL NVARCHAR(MAX) = '';
  
  SELECT @SQL = @SQL + 'INSERT INTO #MyTable
                            EXEC [dbo].[p_UI_Get_My_Tables_Data] ''' + CONVERT(VARCHAR, As_Of_Date, 23) + ''', ''' + @Fund + ''';'
  FROM #AsOfDates;
  
  EXEC sp_executesql @SQL;
  
  SELECT *
  FROM #MyTable
  ORDER BY Fund, Code, As_Of_Date;
  </code></pre>
  
  <p>The final statement in <code>p_UI_Get_My_Tables_Data</code> looks like this:</p>
  
  <pre><code>SELECT
      @AsOfDate AS As_Of_Date, Fund, Code, SUM(Amount) AS Amount
  FROM        
      #DetailedData
  GROUP BY    
      Fund, Print_Group;
  </code></pre>
  
  <p>I have tried executing this code from both my application and SQL Server and usually get an error that says </p>
  
  <blockquote>
    <p>Column name or number of supplied values does not match table definition</p>
  </blockquote>
  
  <p>I have reviewed the code and tried running <code>p_UI_Get_My_Tables_Data</code> by itself and as far as I can tell the table is being returned in the correct format. However, there is one thing that I find strange.</p>
  
  <p>If I open a new query in SQL Server and run my main stored procedure first, I will get the error. However, if I run <code>p_UI_Get_My_Tables_Data</code> by itself first, then my main stored procedure will start working properly. It will continue to work for a little while in SQL Server, but will always fail when called from my application.</p>
  
  <p>I have tried other methods of getting the data I need, but none of them were satisfactory. I tried switching <code>@MyTable</code> to a global temporary table but got the same error. I cannot use a table function because <code>p_UI_Get_My_Tables_Data</code> uses temporary tables. I gave <code>OPENROWSET</code> a shot but starting running into configuration errors and decided it wasn't the best solution.</p>
  
  <p>I think there's a way to accomplish what I am trying to do, since it works part of the time, but I must be missing something in terms of why it is fails at other times. Any advice someone can offer would be much appreciated.</p>
  
  <p><strong>EDIT:</strong> one thing that did work, but is obviously not ideal, was running <code>p_UI_Get_My_Tables_Data</code> from my application and ignoring the results and then running the actual stored procedure. This is the same approach I used to make it work in SQL Server, but I have no idea why it is necessary and would prefer a cleaner solution. </p>