Querying Stored Procedure Results

[agoeddeke]

Hello,

I read in the FAQ that you can't use Stored Procedures in Select queries so maybe there is another obvious way to solve my problem.

I'd like to call the SP_WHO stored procedure in my stored procedure only return certain columns and certain rows. IE: I'd like to return only that status and dbname for those rows who have spid of 51 or greater.

How can I do this?

Thanks,
Andy

 

[NoCoolHandle]

Assuming you are using ADO and this is a webpage (asp) you could do something like

set rs = server.createobject("adodb.Recordset&quot
rs.open "sp_who","provider=sqloledb;server=localhost;trusted_connection=yes"

do until rs.eof
for each fld in rs.fields
response.write fld.value & ","
next
rs.movenext
response.write &quot;<br>&quot;
loop

HTH

Rob

 

[agoeddeke]

Thanks Rob,

Yeah, handling it on the client side is an option, I just thought there might be an ~easy~ way to get the exact results I need.

I also thought about copying the SP_WHO stored procedure as my own and modifying it to my liking.

Not sure which is the best option. I guess I'm glad to have options!

Thanks,
Andy

 

[NoCoolHandle]

Andy,

is you want much more detail and information about what is going on with your server, forget sp_ and go directly at the table data. sp_'s tend to be very inflexable.

the system tables you are looking for are

master.dbo.syslocks
master.dbo.sysprocesses

sysprocesses will probably be the real key to what you want.

Kalen Delane (probably spelt wrong) has written a very good book called inside sql server 2000 that gets into these and many other topics in great detail.


HTH


Rob

 

[agoeddeke]

Thanks for the good info Rob.

Andy