SQL SERVER – Select Columns from Stored Procedure Resultset

It is fun to go back to basics often. Here is the one classic question:

“How to select columns from Stored Procedure Resultset?”

Though Stored Procedure has been introduced many years ago, the question about retrieving columns from Stored Procedure is still very popular with beginners. Let us see the solution in quick steps.

First we will create a sample stored procedure.

CREATE PROCEDURE SampleSP
AS
SELECT 1 AS Col1, 2 AS Col2
UNION
SELECT 11, 22
GO

Now we will create a table where we will temporarily store the result set of stored procedures. We will be using INSERT INTO and EXEC command to retrieve the values and insert into temporary table.

CREATE TABLE #TempTable (Col1 INT, Col2 INT)
GO
INSERT INTO #TempTable
EXEC SampleSP
GO

Next we will retrieve our data from stored procedure.

SELECT *
FROM #TempTable
GO

Finally we will clean up all the objects which we have created.

DROP TABLE #TempTable
DROP PROCEDURE SampleSP
GO

Let me know if you want me to share such back to basic tips.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 4
Next Post
SQL SERVER – Weekly Series – Memory Lane – #048

Related Posts

15 Comments. Leave new

  • Other options to avoid #temptable:
    1) If single query in procedure then we can use view instead of procedure & can use it as table in select query.
    2) if multiple operations then we can also use table valued function instead of procedure & can use it as table in select query.

    procedure requires only if we want to do DML operations in our code block…

    Reply
  • what about multi result set stored proc ?

    Reply
  • Sometimes stored proc returns many columns with a variety of data types. In this case, developer needs to query the result set without creating a temp table because it really becomes a pain. Are there any shorcut solutions for this case?

    Reply
  • Use Openrowset function

    Reply
  • for example:
    select a.*
    from
    openrowset(‘SQLOLEDB’,’SERVER=servername;Trusted_Connection=yes;’,
    ‘ set fmtonly off;
    exec dbname.schemaname.procname’) a

    Reply
  • i am new to this field if you can please tell me if it is possible to do this it will be very help full. what i want to do is from my table i want to take first row and try to use it as a column name to create another table by SP. the reason why i want to use those particular column name is which will come in as data in first row is, it will keep changing every week since those are dates. so my plan is if it is possible to extract that first line from data which is ( [Employee Name], [10/18/2013], [10/19/2013]..) and create another table which will use that extracted data as “column name” and create another SP will fill data as required. but i have no clue if SQL will create table and use “column name” which extracted from another data.

    Thanks
    Rav

    Reply
  • I wonder why I can not give a common select the results of a procedure ?

    Reply
  • Mohan Mudaliar
    January 18, 2018 7:20 pm

    Can anybody help me to get Store Procedure Output Column header from SQL Server

    Reply
  • This won’t work if you have insert into’s in your stored proc. SQL Stops nested insert into statements.

    Reply
  • EXEC sp_describe_first_result_set N’sp_name_in_here’

    Reply
  • I want to take few columns from sp that values I want to insert into table
    Insert into sampletable
    Exec procedure

    Reply

Leave a Reply

Menu