Sean Lange (10/17/2013)
sqlblue (10/17/2013)
I have a stored procedure that needs to be called to update a table inside for a large date range (millions of records), but I wanted this stored procedureto be looped through some kind of batches mechanism so that not too many records being update at a time. What is the best way for me to call this
stored proc in batches? thanks a lot.
declare @startdate datetime, @enddate datetime;
select @startdate=min(datefield), @enddate = max(datefield)
from table;
execute sproc_updatesomething @startdate, @enddate;
It is pretty unclear what you are doing. What do you mean by call the proc in batches?
I agree with Sean in that the request isn't very clear. If you add parameters to a procedure and then pass in the MIN and MAX of a single date field, it'll update the whole table. The only point in including the values in the first place would be to try and force it to use an index.
BTW, if you mean that you want to fire multiple update statements for blocks of dates and put that inside a loop to process N rows at a time, you're going to end up updating the whole table anyway, but taking multiple steps to do it. Because the look will run one iteration after the other, you'll be doing the same net amount of work, but multiple statements would just seem slower than a single, well-tuned update statement. There must be something I'm missing here. Could you please expand on what you're trying to do?