Fastest, easiest way to create a table from a stored procedure.

Question

I use system stored procedures all the time, but always need/want to filter or sort the results of the sproc. I typically use a table variable for this, but defining the fields/datatypes for the table variable is a pain. For instance...

DECLARE @SpWhoResults TABLE
        (
            spid INT,
            STATUS VARCHAR(100),
            loginname VARCHAR(2000),
            hostname VARCHAR(2000),
            blkby VARCHAR(100),
            dbname VARCHAR(200),
            cmd VARCHAR(MAX),
            cputime INT,
            diskio INT,
            lastbatch VARCHAR(100),
            pgmname VARCHAR(500),
            parentspid INT,
            request_id INT
        )

INSERT INTO @SpWhoResults
EXEC sp_who2

SELECT *
FROM   @SpWhoResults
--WHERE  dbname = 'Yourdbname'
ORDER BY dbname

Is there any trick for getting the fields/data types returned by the sproc for quick creation of the table variable declaration for a specific sproc?

Answer 1

I think this link will help you.

http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure