Aggregate values from multiple stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Aggregate values from multiple stored procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-03 : 09:45:34

Robert writes "How do I create a Stored procedure that executes several other stored procedures (that return result sets) and then return the aggregate result set?
The sub-stored procedures all return result sets with the same number of fields and the same field names.

I would think it would be as easy as treating the stored procedures as SQL statements and wrapping them up in another SQL statement (that SUMs the result set columns) but I seem to be having trouble with this approach.

Thanks
Robert Freeman"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 09:52:13

Why not just combine the code for all of the procedures into one main procedure? It's the easiest way to do it.

Alternately, you can use a temp table, create it in the first procedure in the chain, have each subsequent procedure INSERT the results into it, then SELECT from it in the last procedure in the chain (make sure to drop it too)

Jay99

468 Posts

Posted - 2002-04-03 : 09:52:16

When you say 'aggregate result set' what do you mean? Do you mean you just want to UNION them all together or are you trying to group the output by a set of columns and calculates aggregate expressions (MIN, MAX, SUM, and so on)?

Jay
<O>

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-03 : 09:52:19

one way....may not be the best/simplest way....

create temp table
insert results into temp table...
when finished sub-queries....select * from temp table....

Jay99

468 Posts

Posted - 2002-04-03 : 09:53:42

WOW!!! 3 posts in 6 seconds . . .

Jay
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 10:45:13

Notice how the current popularity of realistic war movies has led to increased SNIPING on SQL Team?????

refreeman4
Starting Member

6 Posts

Posted - 2002-04-03 : 15:14:58

I don't want to combine the code into a separate procedure because it would be a duplication that I would have to keep up with as the code changes.
By Aggregate result set I mean I am grouping by one column and calculating (SUM) the rest of the columns.
ex.
Each procedure identifies the Total amount within each Status\state of a specific insurance claim Type.
Type - Brain (procedure)
Status Amount
Submitted $1000
Reviewed $2000
Approved $1500

Type - Heart (procedure)
Status Amount
Submitted $5000
Approved $3500

I want to be able to run all procedures (not duplicating code) and end up with this...
Type - All
Status Amount
Submitted $6000
Reviewed $2000
Approved $5000

Do you guys think a SELECT against a temp table is the most elegant way of accomplishing this? thx for your help!

Jay99

468 Posts

Posted - 2002-04-03 : 15:22:22

either that or


create view AllProcedures
as
select
    Status,
    Amount
from
    Brain
union
select
    Status,
    Amount
from
    Heart
union
select
    Status,
    Amount
from
    YakParts
go

select
    Status,
    SUM(Amount)
from
    AllProcedures
group by 
    Status

Jay
<O>

refreeman4
Starting Member

6 Posts

Posted - 2002-04-03 : 15:36:32

I'll give that a shot... and I totally forgot YakParts so you saved me on that one too!
THX

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources