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

Stored procedure in a SELECT statement

Objective: Execute a stored procedure inside a SELECT statement and return the result as a table. I found a blog that shows how to execute a stored procedure inside a SELECT statement and return the result as a table. Although this can also be achieved using a table-valued function, this is much useful capturing result sets returned by system stored procedure like, sp_who. Although there are a number of ways to do this, I found this very simple technique. There are a number of consideration before this can be used including: user-rights, configuration setting and the size of the result set to be returned. Also, try to consider using function first. What I did is take advantage of the OPENROWSET function that allows user to access remote data from a data source. Here's a sample of retrieving the result set as returned by the system stored procedure sp_who:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'set fmtonly off; exec sp_who') AS a

As this is like a regular SELECT statement, data merge (JOIN, UNION), filter condition (WHERE) and sorting (ORDER BY) can be used. The result may also be inserted into a table using the INSERT INTO...SELECT statement or to create a new table by including a INTO new_table> statement on the SELECT statement. The new_table may also be a temporary table. Here's another sample. Although the following code may be implemented in some other ways, it was created to illustrate how the stored procedure be executed inside a SELECT statement and return the result. Here's the stored procedure:

create proc tproc (@dbname as varchar(50))
as
begin
   declare @sql varchar(500)
   set @sql = 'select * from ' + @dbname + '.dbo.sysobjects'
   exec (@sql)
end

exec dbo.tproc 'model'
exec dbo.tproc 'master'

To call in a SELECT statement and return as a table:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
 'set fmtonly off; exec workdb.dbo.tproc ''master''') AS a;

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
 'set fmtonly off; exec workdb.dbo.tproc ''model''') AS a;

As always, comments are welcome ....


~~CK