September 21, 2011 at 8:56 am
This is a challenge that I haven't mastered yet, and I've been writing SQL for a long time.
There are often many instances when I have a stored procedure and I need to call it iteratively based on a specific result set. So what happens is each row in the result set maps to the parameters that are needed by the stored proc.
Usually I have these parameters stored in a temporary table which I create with an integer identity column and then use the identity min and max values to control my while loop.
Other approaches to this same problem have involved using cursors, but I try to steer my development staff away from cursors in these scenarios.
I know that many dba's use tally tables to replace loops, but I don't understand how a tally table would help in this scenario.
Any suggestions would be appreciated. We are currently using SQL Server 2008 R2 in all environments (dev, test, prod).
September 21, 2011 at 9:05 am
Can't use tally here.
Copy the SP you're copy.
Add a temp table at the top and fill it with what you need to process.
Then join that table to all subsequent steps so that you process all the data in 1 go.
September 21, 2011 at 9:16 am
I don't quite understand your response.
I have a temp table with an integer identity column as well as a column for each parameter of the stored proc.
Now how does the join work/look?
thx
September 21, 2011 at 9:24 am
I guess in your proc you have <many> statements like
SELECT whatever from dbo.table where col = @p1 and col2 = @p2
Just add an extra join to your temp table with parameter list and swap @p1 & @p2 with the columns in the temp table.
You may have to tweak stuff here & there but it's usually straight forward at that point.
September 21, 2011 at 9:27 am
he's advocating changes the stored procedure to handle the problem as a set rather than one "seeding row" at a time. So rather than looping through and calling for each row, you call the SP once with the whole set, and have it do the work all at once.
If you don't do that (or can't for some reason), then some form of looping will be your only option. It basically won't matter that the loop happens to be within a cursor or a while loop, and the tally table mechanism won't be useful.
2008 has some nifty things you can do with table parameters, if your situation fits that pattern. Otherwise look at an option like was mentioned earlier: leverage a working table which is preloaded with the rows you need to process before calling the SP.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 21, 2011 at 9:31 am
I guess the point of calling the stored procedure iteratively is that I need to keep the logic in the procedure encapsulated.
The procedure logic in these cases is usually very complicated and the reason to encapsulate it is to keep it that way so that it can be called by different applications.
I think your suggestion is to take the logic out of the procedure so that I can join with it directly, but in that's not an option here as the logic contained within the proc needs to remain encapsulated in that stored proc.
September 21, 2011 at 9:33 am
Ok, then list & loop.
September 21, 2011 at 9:38 am
Thx everyone,
The two suggestions have started me thinking ... it might be possible to redesign the procedures in these situations to take a table variable as an input parameter.
Then I could rework the procedure code itself to join against the input table and perform the sql that way ... instead of seeding one row at a time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply