CTE with dynamic query

Question

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

Answer 1

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.

Answer 2

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.