Capturing The Result Set Returned by Stored Procedure

Previously, I wrote a code snippet on how to retrieve the result set returned by a stored procedure and treat it as a select statement. Here is the previous post. OPENROWSET function, however, sometimes can lead to a resource hug.

Here's a modified version of the code that uses a table variable:

declare @spWhoTable as table
(
   col_spid int,
   col_ecid int,
   col_status varchar(20),
   col_loginame varchar(20),
   col_hostname varchar(20),
   col_blk varchar(20),
   col_dbname varchar(20),
   col_cmd varchar(50),
   col_request_id int
)
insert into @spWhoTable
exec sp_who

select 
   *
from @spWhoTable

If the result will be used later that requires it to be indexed, a temporary table may be used instead of a table variable.

As always, comments are welcome and highly appreciated.


~~ CK