Search

Custom Search

Tuesday, July 26, 2011

Inserting result of Stored Procedure to a table

I haven't thought that I can use the result of a stored procedure to another stored procedure. If I have this need I usually convert the SP to user define function to return a table then use that inside the SP.

Until I came across to this search where I want to filter the result of the SP_WHO2 by adding where condition on it. SP_WHO2 is a system stored procedure to return record of threads running on the database instance.

Thanks for this code from http://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2


Declare @Table TABLE(SPID INT,
Status VARCHAR
(MAX),
LOGIN VARCHAR
(MAX),
HostName VARCHAR
(MAX),
BlkBy VARCHAR
(MAX),
DBName VARCHAR
(MAX),
Command VARCHAR
(MAX),
CPUTime INT
,
DiskIO INT
,
LastBatch VARCHAR
(MAX),
ProramName VARCHAR
(MAX),
SPID_1 INT
,
REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT *
FROM @Table
WHERE LOGIN = 'vince'

No comments:

Post a Comment

Adsense Banner