1

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?

APC
  • 144,005
  • 19
  • 170
  • 281
JKMajcen
  • 962
  • 1
  • 8
  • 7
  • possible duplicate of [How to SELECT \* INTO \[temp table\] FROM \[stored procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) – Martin Smith Jul 06 '14 at 22:23

0 Answers0