<< Visual Studio failed to create project | Home | Executing OLEDB SQL statement from C# >>

Selecting all rows from a stored procedure

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

posted @ Thursday, August 20, 2009 12:46 AM

Print

Comments on this entry:

 re: Selecting all rows from a stored procedure

Left by Martin Soles at 8/20/2009 2:16 PM
Gravatar

There is another method, as well, if you know the shape of the data you're getting back. Create a table (variable or a temporary one) that matches the resultset data types for the fields. The names aren't important. Now, you can do a simple INSERT EXEC statement.

INSERT INTO #TMP (Field1, Field2, Field3)
EXEC dbo.PROC.

# re: Selecting all rows from a stored procedure

Left by Eric Stott at 8/21/2009 12:45 PM
Gravatar

Yes, that is a cool way, however, you then have to create a different select statement:
select * from #TMP

Your comment:



 (will not be displayed)


 
 
 
Please add 3 and 8 and type the answer here:
 

Live Comment Preview:

 
«March»
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910