title: CTE with dynamic query
body: <p>I have a dynamic query which I call and I put the Result set in a variable table</p>
  
  <pre><code>INSERT INTO @outTable 
  
  EXEC sp_executesql @query;
  </code></pre>
  
  <p>The problem is that <code>@outTable</code> should change When adding column in <code>@query</code> - <code>@query</code> is another stored procedure.</p>
  
  <p>I tried to use a CTE (<code>WITH</code>), but it does not work with <code>EXEC</code>.</p>
  
  <p>Is this possible do to in T-SQL?</p>
  
  <p>Something like this</p>
  
  <pre><code>DECLARE @outTable TABLE (
                              ID int,
                              Record_ID int, 
                              Order_ID nchar(16),
                              ...and around 30 columns
                          );  
  
      SET @query = N'EXEC [OrderDep].[Order_Find] @FreeWhere =N'' WHERE '+ @Where +'''';
  
      BEGIN TRY 
          INSERT INTO @outTable 
              EXEC sp_executesql @query;
  
      END TRY     
      BEGIN CATCH
  </code></pre>