Calling a Stored Procedure from inside of a loop..

Hello,
I know calling a stored procedure from inside of loop is something that
we all want to aviod, and the most of times there's a better
alternative.
However, I am afraid this is one of the few exceptions that I really
have to call a stored procedure from inside of loop. Just in case you
wonder why, there is a stored procedure which takes one parameter,
namely member_ID, and I would like to test it out against about 12,000
rows in the members table.
So, basically I would like to call this stored procedures about 12,000
times, each time with a different member ID, just to make sure the
stored procedure works for each member.
I know it kinda sound really anal, but because of the complexity of the
stored procedure, mainly due to the poor database design done in years
ago (or so I blame :), despite the fact it only receives one paramter,
there have been instances that the stored procedure did not return a
row for a certain group of members.
I've been working on the stored procedure to make it handel some of
those "special" situations, so that it always returns a row, even if it
is populated with some crooked values. Now I think I've finished
taking care of all that... but, I will definitely sleep better if I can
test it out. So, here I am.
There is a While Loop in TSQL that seems like the one I should use for
this task, but the problem I am having is how to call my stored
procedure with a different member ID in each iteration.
Please remember that, for this particular situation altering the stored
procedure to query a table of member ID's instead of a single member ID
is not an option. Again, because of the complexity of the problem I
need to solve, I was not able to squeeze the query into a single Select
statement in my procedure. It contains a number of calculations, which
use different set of parameters depending on the group the member
belongs to...
Yes, this probably wasn't good idea to do all this conditional
calculations in the stored procedure, but that is how our application
works - probably a really bad design, of course which wasn't done by me
:)
All in all, it's too late to make any kind of architectural changes at
this point, and I am stuck with a task that I have to write a stored
procedure that does all the wonders and returns a row when called with
a single parameter, member ID, no matter how inefficient and slow it
gets.
Since I am trying to do this exhaustive testing for my own sake and on
my own, using a small test code outside of the database using a more
suitable programming language isn't going to be easy, either.
In other words, I need to call my stored procedure which takes one
parameter within a loop with supplying a different parameter each time,
and I need to do this in TSQL.
Thank you very much for your help in advance!Consider these options as well, if they fit your needs:
1) a user-defined function that gets called 12K times while executing a
query inside a stored proc, e.g.
SELECT MemberId, dbo.DoMembersCalc(member_ID) as CalculatedMemberValue
FROM Members
2) a calculated column
SELECT MemberId, CalculatedMemberValue
FROM Members
3) a view
SELECT MemberId, CalculatedMemberValue
FROM MemberView
You can subquery any of these statements to see if any particular
MemberId had an undesireable result in the CalculatedMemberValue
column.
jung_h_park@.yahoo.com wrote:
> Hello,
> I know calling a stored procedure from inside of loop is something that
> we all want to aviod, and the most of times there's a better
> alternative.
> However, I am afraid this is one of the few exceptions that I really
> have to call a stored procedure from inside of loop. Just in case you
> wonder why, there is a stored procedure which takes one parameter,
> namely member_ID, and I would like to test it out against about 12,000
> rows in the members table.
> So, basically I would like to call this stored procedures about 12,000
> times, each time with a different member ID, just to make sure the
> stored procedure works for each member.
> I know it kinda sound really anal, but because of the complexity of the
> stored procedure, mainly due to the poor database design done in years
> ago (or so I blame :), despite the fact it only receives one paramter,
> there have been instances that the stored procedure did not return a
> row for a certain group of members.
> I've been working on the stored procedure to make it handel some of
> those "special" situations, so that it always returns a row, even if it
> is populated with some crooked values. Now I think I've finished
> taking care of all that... but, I will definitely sleep better if I can
> test it out. So, here I am.
> There is a While Loop in TSQL that seems like the one I should use for
> this task, but the problem I am having is how to call my stored
> procedure with a different member ID in each iteration.
> Please remember that, for this particular situation altering the stored
> procedure to query a table of member ID's instead of a single member ID
> is not an option. Again, because of the complexity of the problem I
> need to solve, I was not able to squeeze the query into a single Select
> statement in my procedure. It contains a number of calculations, which
> use different set of parameters depending on the group the member
> belongs to...
> Yes, this probably wasn't good idea to do all this conditional
> calculations in the stored procedure, but that is how our application
> works - probably a really bad design, of course which wasn't done by me
> :)
> All in all, it's too late to make any kind of architectural changes at
> this point, and I am stuck with a task that I have to write a stored
> procedure that does all the wonders and returns a row when called with
> a single parameter, member ID, no matter how inefficient and slow it
> gets.
> Since I am trying to do this exhaustive testing for my own sake and on
> my own, using a small test code outside of the database using a more
> suitable programming language isn't going to be easy, either.
> In other words, I need to call my stored procedure which takes one
> parameter within a loop with supplying a different parameter each time,
> and I need to do this in TSQL.
> Thank you very much for your help in advance!|||For this sort of testing scenario, a cursor is a perfectly viable
option (of course, that goes with the usual disclaimer about avoiding
cursors in production code, they are inherently evil, and bad things
will happen to you if you forsake this advice, don't get them wet, and
never ever feed them after midnight....).
Look up cursors in the Books Online. and that should get you pointed in
the right direction.
HTH,
Stu
jung_h_park@.yahoo.com wrote:
> Hello,
> I know calling a stored procedure from inside of loop is something that
> we all want to aviod, and the most of times there's a better
> alternative.
> However, I am afraid this is one of the few exceptions that I really
> have to call a stored procedure from inside of loop. Just in case you
> wonder why, there is a stored procedure which takes one parameter,
> namely member_ID, and I would like to test it out against about 12,000
> rows in the members table.
> So, basically I would like to call this stored procedures about 12,000
> times, each time with a different member ID, just to make sure the
> stored procedure works for each member.
> I know it kinda sound really anal, but because of the complexity of the
> stored procedure, mainly due to the poor database design done in years
> ago (or so I blame :), despite the fact it only receives one paramter,
> there have been instances that the stored procedure did not return a
> row for a certain group of members.
> I've been working on the stored procedure to make it handel some of
> those "special" situations, so that it always returns a row, even if it
> is populated with some crooked values. Now I think I've finished
> taking care of all that... but, I will definitely sleep better if I can
> test it out. So, here I am.
> There is a While Loop in TSQL that seems like the one I should use for
> this task, but the problem I am having is how to call my stored
> procedure with a different member ID in each iteration.
> Please remember that, for this particular situation altering the stored
> procedure to query a table of member ID's instead of a single member ID
> is not an option. Again, because of the complexity of the problem I
> need to solve, I was not able to squeeze the query into a single Select
> statement in my procedure. It contains a number of calculations, which
> use different set of parameters depending on the group the member
> belongs to...
> Yes, this probably wasn't good idea to do all this conditional
> calculations in the stored procedure, but that is how our application
> works - probably a really bad design, of course which wasn't done by me
> :)
> All in all, it's too late to make any kind of architectural changes at
> this point, and I am stuck with a task that I have to write a stored
> procedure that does all the wonders and returns a row when called with
> a single parameter, member ID, no matter how inefficient and slow it
> gets.
> Since I am trying to do this exhaustive testing for my own sake and on
> my own, using a small test code outside of the database using a more
> suitable programming language isn't going to be easy, either.
> In other words, I need to call my stored procedure which takes one
> parameter within a loop with supplying a different parameter each time,
> and I need to do this in TSQL.
> Thank you very much for your help in advance!