December 14, 2016 at 2:14 pm
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.
December 14, 2016 at 2:24 pm
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.
December 14, 2016 at 3:08 pm
It sounds like what you really want is an inline table-valued function.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 14, 2016 at 3:23 pm
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
December 15, 2016 at 3:13 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 15, 2016 at 3:48 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply