Matt Miller (#4) (9/22/2009)
This is bound to go *boom* if ANYONE is doing inserts on the table while you're running this loop.
What is the inner stored procedure doing? This approach tends to be about the worse you can get in terms of performance, since you're forcing SQL Server to work one row at a time. SQL Server is a "set engine" meaning it performs best against a LOT of records at the same time, so throttling it like this is kind of like buying a porsche, and just using it for the ashtray.
Well, part of this is theoretical, since I run upon this type of situation fairly often. In this particular case it may be possible to modify the inner stored proc to handle all the work against a group of records, rather than against one record.
But the general idea is to do an "upsert" - insert if the record isn't found, update if the record is found. What I could probably do is split the batch into two - one batch which will insert, and the other batch which will update.
However, if the situation was such that it was simply not possible, and that it had to be done such that a stored proc or batch of code was executed for each record, is the solution which I posted, or variations of it, the only option?