Insert into #temp_table from stored procedure with dynamic columns-CodePudding

I have a stored procedure which can return a variety of field names in its output. Something like this:

create or alter procedure dbo.my_prc (@return_format as int ) as
    create table #all_data (id int ,chr varchar(10))
    insert into #all_data
    values (1,'a')
        ,(2,'b')
        ,(3,'c')

      --return id -> id_2 only
     if @return_format = 1
          select id * 2 as 'id_2' from #all_data

      --return chr -> chrq only
     if @return_format = 2
          select chr   '?' as 'chrq' from #all_data

     --return everything
     if @return_format = 3
          select * from #all_data

Here are possible outputs:

exec my_prc @return_format = 1 --id_2
exec my_prc @return_format = 2 --chrq
exec my_prc @return_format = 3 --everything

How can I dynamically create a #temp_table (or a set of temp tables for 1-3) which will capture my field names and data?

This obviously doesn't work but something like this is what I'm thinking:

/*
exec my_prc @return_format = 1 into #temp_table

--or maybe

select top 0 from (exec my_prc @return_format = 1) into #temp_table --create a #temp_table with the field names
insert into #temp_table exec my_prc @return_format = 1
*/

For more context, I am trying to avoid simply declaring a static #temp_table and inserting into as such. I don't want to get into the weeds, but that is likely not a good option for me.

create table #temp_table (id int ,chr varchar(10))
insert into #temp_table
exec my_prc @return_format = 3

CodePudding user response：

Here is a solution which writes to the database then creates a #temp_table from that newly created table

The stored procedure can look like this

create or alter procedure dbo.my_prc (@return_format as int ) as
    create table #all_data (id int ,chr varchar(10))
    insert into #all_data
    values (1,'a')
        ,(2,'b')
        ,(3,'c')

     drop table if exists dbo.output_table --drop so that you can reset the field names

      --return id -> id_2 only
     if @return_format = 1
          select id * 2 as 'id_2' into dbo.output_table from #all_data

      --return chr -> chrq only
     if @return_format = 2
          select chr   '?' as 'chrq' into dbo.output_table from #all_data

     --return everything
     if @return_format = 3
          select * into dbo.output_table from #all_data

     select * from dbo.output_table --if you still need to see the output in the SP

And then you can create your #temp_table off dbo.output_table

select * into #temp_table from dbo.output_table

Regardless of which value you choose for @return_format, dbo.output_table will have those fields

CodePudding user response：

Have you tried to use Dynamic SQL?

It is a way to write your SQL in a string and then execute it.

e.g.

v_sql = "create table "   v_table_name   for loop with columns... ";

execute immediate v_sql;