Best way to calculate totals in a Stored Procedure

[fpendino]

I'm using Sql Server 2000 and ASP to display this info. My question is, what is the best way to display data w/totals onto a page using a stored procedure. I've built stored procedures before that include totals, but in this case, doing so will cause there to be prob like 100+ columns. I also know that I could call a stored procedure for the detail and output, and then call a stored procedure for the totals and output.

I don't want to go this route though, as on my main summary page, I would have to do that several times and I think that there would be performance issues. There are multiple grouping levels within this page. So basically, I'm wanting the easiest way of outputting multiple columns and rows that include Totals for both, without having a query that has 100+ columns.

Can a stored procedure produce multiple Select statements, to where I can have it output totals and detail separately, but within one stored procedure?

 

[pdx_man]

You can have the SP output several recordsets and then cycle to the next recordset your DataReader in ASP.

Here, let me do a quick Google search;

http://www.velocityreviews.com/forums/t72012-multiple-recordset-from-a-stored-procedure.html

 

[pbaldy]

One thing I've done when I want both detail and totals in the same query is to union a query of the detail with another with the totals (or subtotals).

 

[fpendino]

Thanks for both of the replies! I did happen to find out about the NextRecordset function, and thought that it was going to work for what I was wanting, but it won't because I would have to come back to the first and second recordset. My page will display a couple different layers of detail and totals due to the groupings that I have, but otherwise I may use that for other reports in the future.

I may be able to somehow use the union query though. It will take a little bit of adjusting, but I think I should be able to come up with something to make that work. It won't be fun!

Thanks again!

 

[pdx_man]

In that case, use a data adaptor to fill in the tables and then you can reference each table in any order and go back and forth and up and down and ... with your recordsets.

 

[fpendino]

It looks like you are referring to ASP.net, unfortunately I'm using original ASP. I appreciate the help though. I'm eventually going to switch this report over to asp.net, but I'm more familiar with original ASP at this time. I'll defenitely keep that post as a reference though for the future.

Thanks!

 

[pdx_man]

Original ASP, eh? I didn't know there were still any of you out there.

Come on over ... the water is nice.