How to access multiply query info in a Stored Procedure

[roccorocks]

How do I retrieve information from a stored procedure with multiple queries? My stored procedure looks something like this below:

CREATE PROCEDURE spCount1

@dateIn smalldatetime,
@listN int

as

select class, count(class) as count1
from accountinfo
where (listnum = @listn) and (lastin = @datein) and curbalance <=250
group by class

select class, count(class) as count2
from accountinfo
where (listnum = @listn) and (lastin = @datein) and curbalance >250
group by class

go

I am passing 2 values and want to return both count1 and count2. Currently only the last query performed is accessible (the data).

In the query analyzer the information is displayed in multiple grids. I have gone through some of my SP books and have not found any information on this (do I need to set up cursors? If so, how?)

thanks,

Rocco

 

[mkrausnick]

How about using UNION:
select &quot;A&quot; as id, class, count(class) as count1
from accountinfo
where (listnum = @listn) and (lastin = @datein) and curbalance <=250
group by id, class
UNION
select &quot;B&quot; as id, class, count(class) as count2
from accountinfo
where (listnum = @listn) and (lastin = @datein) and curbalance >250
group by id, class

Your result set would contain two sets of records, identified by &quot;A&quot; or &quot;B&quot;, that would contain the counts you are looking for.

I'm not sure whether the id would be required in the group by, since it is a constant, but I put it there just to be safe.




Mike Krausnick

 

[roccorocks]

Thanks for the help Mike. I will try this out, however, I figured out something else I could use. I am using the nextrecordset property of the ADODB recordset. Like this:

do until rs is nothing

set rs = rs.nextrecordset
loop

I do appreciate your help!

Rocco

 

[CurtisJM]

If you don't care about recordsets and just want to use output parameters - which is the way I would do it for scalar values - then just declare two output parms and then use SET or SELECT to assing the scalar values to @variables... if you want more spefics let me know.

The advantage of this is performance and code simplification.

 

[roccorocks]

If you could give a specific example, emmulating what I did above, I would greatly appreciate it.

 

[CurtisJM]

Sorry for the delay... While my recordset comment is valid, it might not be in your example cuz you wanted the count by class... So, you have to retrieve multple recordsets. Be happy that you don't have to do this in Oracle. Anyway, take a look at the section titled &quot;Generating Multiple Recordsets&quot; in Books On Line under ADO and SQL Server, ... Executing Stored Procedures...

In short, I think you are doing this the correct way now.