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 (http://blog.SQLAuthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL