Aug 202009
 

I needed to use a select to return data, however the data resided via a call to a stored procedure.

For a simple example, I will use the the following stored procedure

exec sp_who 'sa'

original.sp_who.results

So how do I select * from a stored a procedure?

Use OPENROWSET

select *
from openrowset('sqloledb','Server=(local);TRUSTED_CONNECTION=YES','set fmtonly off exec master.sys.sp_who ''sa''')

which returns this result

selected.sp_who.results

Which means that I can filter what I want:

select *
from openrowset('sqloledb','Server=(local);TRUSTED_CONNECTION=YES','set fmtonly off exec master.sys.sp_who ''sa''') [x]
where x.status='sleeping'

subselected.sp_who.results