May 15, 2014

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 SampleSPAS
SELECT 
AS Col1AS Col2UNION
SELECT 
1122
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 INTCol2 INT)GOINSERT INTO #TempTableEXEC SampleSP
GO
Next we will retrieve our data from stored procedure.
SELECT *FROM #TempTableGO
Finally we will clean up all the objects which we have created.
DROP TABLE #TempTableDROP PROCEDURE SampleSP
GO

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib