Insert into distinct columns from a stored procedure

Question

How to insert into a temp table that is all ready created inside of a stored procedure

ALTER PROCEDURE [dbo].[Report_1]
BEGIN
    CREATE TABLE #Temp
    (
        col1 INT,
        col2 INT,
        col3 VARCHAR(50)
    )

    INSERT INTO #Temp
       EXEC [spSelection] @ID

    ..do stuff
    ..do stuff
    ..do stuff

    SELECT * FROM #temp
END

The problem I am having is, I will use this stored procedure (spSelection) in the future and if I change this stored procedure to get more columns for a different stored procedure, then Report_1 will fail.

So I need a way to dynamically create the table or be able to only select distinct columns from the output of exec [spSelection] @ID or have Report_1 be able to read from a temp table created in spSelection.

I have tried to use a global and that will not work because it can be used by other stored procedure at the same time, if I create a dynamic SQL.

@sql ='
    create table #Temp(
    col1 int,col2 int,col3 varchar(50)
    ) ' exec sp_executesql @sql

I can not access the #temp table outside of the quotes

It's a STORED procedure - as in a procedure that is stored inside your SQL Server - not a "storage procedure" ... — marc_s

EzLo EzLo · Accepted Answer · 2019-02-28T08:00:23

One alternative is to change your SP to make the insert inside the SP:

ALTER PROCEDURE [spSelection]
AS
BEGIN

    -- Validate that your temporary table was created (the insert will fail otherwise)
    IF OBJECT_ID('tempdb..#Temp') IS NULL
    BEGIN
        RAISERROR ('The table #Temp must be created before executing this SP', 16, 1)
        RETURN
    END

    ..do stuff
    ..do stuff
    ..do stuff

    INSERT INTO #Temp (
        col1,
        col2,
        col3)
    SELECT
        /*Columns*/

END
GO


ALTER PROCEDURE [dbo].[Report_1]
BEGIN

    CREATE TABLE #Temp
    (
        col1 INT,
        col2 INT,
        col3 VARCHAR(50)
    )

    EXEC [spSelection] @ID -- Will insert into #Temp

    ..do stuff
    ..do stuff
    ..do stuff

    SELECT * FROM #temp
END

This approach will fail if you eventually add new columns to the #Temp table and insert them inside the SP without updating the CREATE TABLE in every SP that calls it.

There is no definitive solution here, please read this excellent paper about all possible ways to share data between SP, with pros and cons of each (the solution I posted here is listed as 4. Using a table).

Insert into distinct columns from a stored procedure

2 Answers