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'
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
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'