Save dynamic result from stored procedure into a dynamic table

Question

I am facing some issues in saving the execution of stored procedure / scalar function into a table variable.

The function / stored procedure returns dynamic columns and I need to create a dynamic table to save the result of that function into it so that I can use the table.

Example: the stored procedure spGetEmployeeInfo could return employee name, employee id, etc. on such criteria they return only employee name,.

Is there a way to create a dynamic table and save the result into it after execute the stored procedure, or any suggestion.

Thanks

*"is there a way to create a dynamic table"* Use a `SELECT...INTO`? You'll need to do it *inside* the dynamic statement though, not outside of it. — Thom A, Feb 17 '21 at 17:07

score 1 · Answer 1 · answered Feb 17 '21 at 17:15

I don't like to get into this situation too often, but when I do, what I do is have the stored proc output into a global temp table. The name of the table is passed in as a parameter by the user. For instance:

create procedure dynamicBeCareful 
    @toggle bit, 
    @globalTempTableName varchar(50) 
as 

-- initializations

    if left(@globalTempTableName,2) <> '##'
      throw 50000, '@globalTempTableName must start with ##', 1;

    declare @sql varchar(max);

-- build dynamic sql

    if @toggle = 1 
        set @sql = 'select * into @tempTable from table1';
    else 
        set @sql = 'select * into @tempTable from table2';

    set @sql = replace(@sql, '@tempTable', @globalTempTableName);

-- terminations

    exec (@sql); 

    declare @msg = 'Your results are in ' + @globalTempTableName;
    print (@msg);

Then use it like this:

exec dynamicBeCareful 1, '##temp';

select * from ##temp;

Beyond just being dynamic in output, it also can get you out of nested insert-exec limitations.

Save dynamic result from stored procedure into a dynamic table

1 Answers1