SQL SERVER – Select Columns from Stored Procedure Resultset

 https://blog.sqlauthority.com/2013/09/26/sql-server-select-columns-from-stored-procedure-resultset/

First we will create a sample stored procedure.

1 2 3 4 5 6

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.

1 2 3 4 5

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

Next we will retrieve our data from stored procedure.

1 2 3

SELECT * FROM #TempTable GO

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

1 2 3

DROP TABLE #TempTable DROP PROCEDURE SampleSP GO

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