CTE with dynamic query

I have a dynamic query which I call and I put the Result set in a variable table

INSERT INTO @outTable 

EXEC sp_executesql @query;

The problem is that @outTable should change When adding column in @query - @query is another stored procedure.

I tried to use a CTE (WITH), but it does not work with EXEC.

Is this possible do to in T-SQL?

Something like this

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

When using insert ... exec ..., the table definition must match the output from the stored procedure.

Since you're creating the @query statement, you probably know what it's going to return. It may be a lot of work, but you'll have to adjust the table definition by hand.

Given what we know of the problem so far, the way to solve this problem is to either fork the logic to use different queries with different destination tables or use dynamic SQL. If the number of destination tables is small, then I'd recommend the former solution which would look like:

If @Parameter = 'SomeValue'
    Insert TableA
    Select ..
    From ...

Else If @Parameter = 'SomeOtherValue'
    Insert TableB
    Select ..
    From 

I would recommend against trying to make an all encompassing function that can take any destination table name and the text of a select query and make an insert statement if that is what you are trying to achieve.

It sort of depends what you want that variable for.

I suspect though that your easiest way out would be to store the data in pivoted form and leave it that way all the way until it's to be returned to the client application. Then a stored proc can build the SQL needed to unpivot the result.

Store and process something like this:

ID       Col         Value
-----------------------------------------------------
1        FName       Dag
1        Email       yeah.right@maybe.no
1        Col3        Value3
...
N        FName       Kristina
N        Email       my.first.love@sadness.info
N        Col3        Value3
N        Col4        Dag hasn't got this attribute

When it's time to return the data to the client app, and not before, a stored proc can dynamically generate the SQL to unpivot the data into this:

ID    FName     Email                        Col3      Col4
------------------------------------------------------------------------------------
1     Dag       yeah.right@maybe.no          Value3    (null)
....
N     Kristina  my.first.love@sadness.info   Value3   Dag hasn't got this attribute.

Of course, the column set will be as large as the set of distinct Name values in the pivoted data table. But this works well if you store many kinds of objects in a single table but select only sets of items with the same (or at least similar) sets of attributes. Another potentially important restriction: EVERYTHING will be just text, so consider the implications of that.

However, I've been thinking about making use of this technique to make a DAL for rapid prototyping purposes. It is obviously very costly in time and space compared to normalized tables but it does make it possible to store, retrieve and process smaller quantities of data in a generic fashion.

To unpivot, generate SQL using a pattern like this:

SELECT 
    ID,
    MAX(CASE WHEN [Name] = 'FName' THEN [Value] END [FName]),
    MAX(CASE WHEN [Name] = 'Email' THEN [Value] END [EMail]),
    ...
FROM 
    Stuff
GROUP BY 
    ID

As long as there's at most one row with Name = 'FName' this works out well, since we group by ID. And generating the SQL is straightforward; only the CASE bit needs any formatting:

DECLARE @case varchar(max)
SET @case = 'CASE WHEN [Name] = ''§name'' THEN [Value] END [§name])'

-- Get columns:
SELECT Column_Name INTO #cols FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @tableName;

-- in loop (can't be bothered to include the code!)
SET @sql = @sql + REPLACE(@case, '§name', @colName)
...
EXEC(@sql)