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