calling stored procedure in a loop and selecting from results

Question

calling stored procedure in a loop and selecting from results

junk.mail291276

SSCrazy

Points: 2503
More actions
December 14, 2016 at 2:14 pm

#332788

Hello,
I have a stored procedure that takes a project ID as a parameter and returns a list of results on that project. Now what I'd like to do is create another stored procedure that takes a list of project IDs as a parameter, and cycles through each one calling the first stored procedure. The second stored procedure needs to SELECT from the cumulative results of all the first stored procedure calls.
For example, the first stored procedure has these two columns (among others): CATEGORY, and TOTAL_CREDITS.
If for project 1, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 3
MEC, 4
and if for project 2, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 9
MEC, 15
The second stored procedure needs to select the sum of TOTAL_CREDITS for each CATEGORY:
CATEGORY, TOTAL_CREDITS
BPCS, 12
MEC, 19
How can this be done?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

junk.mail291276 (12/14/2016)
Hello,
I have a stored procedure that takes a project ID as a parameter and returns a list of results on that project. Now what I'd like to do is create another stored procedure that takes a list of project IDs as a parameter, and cycles through each one calling the first stored procedure. The second stored procedure needs to SELECT from the cumulative results of all the first stored procedure calls.
For example, the first stored procedure has these two columns (among others): CATEGORY, and TOTAL_CREDITS.
If for project 1, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 3
MEC, 4
and if for project 2, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 9
MEC, 15
The second stored procedure needs to select the sum of TOTAL_CREDITS for each CATEGORY:
CATEGORY, TOTAL_CREDITS
BPCS, 12
MEC, 19
How can this be done?
Thanks.

I wouldn't do it that way. I would write a query that meets the requirements you stated. I'd start with the query in the first stored procedure as a starting point for the query.

drew.allen SSC Guru Points: 76989 More actions · Answer 2

It sounds like what you really want is an inline table-valued function.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Lynn Pettis (12/14/2016)
junk.mail291276 (12/14/2016)
Hello,
I have a stored procedure that takes a project ID as a parameter and returns a list of results on that project. Now what I'd like to do is create another stored procedure that takes a list of project IDs as a parameter, and cycles through each one calling the first stored procedure. The second stored procedure needs to SELECT from the cumulative results of all the first stored procedure calls.
For example, the first stored procedure has these two columns (among others): CATEGORY, and TOTAL_CREDITS.
If for project 1, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 3
MEC, 4
and if for project 2, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 9
MEC, 15
The second stored procedure needs to select the sum of TOTAL_CREDITS for each CATEGORY:
CATEGORY, TOTAL_CREDITS
BPCS, 12
MEC, 19
How can this be done?
Thanks.
I wouldn't do it that way. I would write a query that meets the requirements you stated. I'd start with the query in the first stored procedure as a starting point for the query.

Absolutely! Iterative processing in SQL Server is a quick path to poor performance! The verbal description and results you posted sounds like a simple query similar to this:

SELECT category, sum(total_credits) from something group by category

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

drew.allen (12/14/2016)
It sounds like what you really want is an inline table-valued function.
Drew

I was thinking that, or a TVP that takes a list of IDs and then joins to the table.

joeroshan SSChampion Points: 10377 More actions · Answer 5

Lynn Pettis (12/14/2016)
junk.mail291276 (12/14/2016)
Hello,
I have a stored procedure that takes a project ID as a parameter and returns a list of results on that project. Now what I'd like to do is create another stored procedure that takes a list of project IDs as a parameter, and cycles through each one calling the first stored procedure. The second stored procedure needs to SELECT from the cumulative results of all the first stored procedure calls.
For example, the first stored procedure has these two columns (among others): CATEGORY, and TOTAL_CREDITS.
If for project 1, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 3
MEC, 4
and if for project 2, it returned these results:
CATEGORY, TOTAL_CREDITS
BPCS, 9
MEC, 15
The second stored procedure needs to select the sum of TOTAL_CREDITS for each CATEGORY:
CATEGORY, TOTAL_CREDITS
BPCS, 12
MEC, 19
How can this be done?
Thanks.
I wouldn't do it that way. I would write a query that meets the requirements you stated. I'd start with the query in the first stored procedure as a starting point for the query.

I think this is the best approach. Try to identify logic of first SP and change it to take table valued parameters so that you can send multiple inputs . You might be able to just use joins and aggregation to achieve the result.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]