How to store result of a stored procedure in a table variable (Sql Server)

Table variables are very useful to store intermediate results in between a dynamic query or a large query whose results need to be fed into another sql query. Hence there are many times, when we encounter a situation where we need to store result of a stored procedure execution into a table variable to be used further by another query.
Let’s see how we can do that. For this first I have Created some sample table & Data.

--Table Creation
CREATE TABLE [dbo].[STATES](
 [STATE_ID] [INT] IDENTITY(1,1) PRIMARY KEY,
 [STATE_NAME] [VARCHAR](50) NOT NULL,
 [STATE_CODE] [VARCHAR](2) NOT NULL,
 [ROW_STATUS] [INT] NULL,
);
--Inserting Rows
INSERT INTO STATES 
VALUES('Alabama','AL',1)
,('Alaska','AK',2)
,('Arizona','AR',1)
,('California','CA',2)
,('Colorado','CO',1)
,('Connecticut','CT',2)
,('Delaware','DE',1)
,('Florida','FL',2)
,('Georgia','GA',1)
,('Hawaii','HI',2);

Now I have Created a Stored Procedure which returns data from table States based on ROW_STATUS

--STORED PROCEDURE
CREATE PROC usp_GetStatesAsPerSatus
@RowStatus INT
AS
BEGIN
 
SELECT * FROM States WHERE ROW_STATUS = @RowStatus
 
END
 now lets see how we can store result of this SP in a table variable.
--Create a Table variable having same number of columns as we have from sp output
DECLARE @tblSpData TABLE
(
ID INT IDENTITY(1,1), --Extra column for row number
STATE_ID INT ,
STATE_NAME VARCHAR(50),
STATE_CODE VARCHAR(2),
ROW_STATUS INT
)
 
--Insert data into table variable from SP output
INSERT INTO @tblSpData
EXEC usp_GetStatesAsPerSatus 2
 
--Check Result
SELECT * FROM @tblSpData

Insetead of table variable user can also create Temp tables to store sp result.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s