Edward.Polley 76944 (3/7/2013)
BTW - Ceiling works here because these are integer variables.
It doesn't work in your algorithm. I've pulled it apart to see what it does if there were 5001 rows that needed deleting and it was doing it in batches of 2000.
DECLARE @N INT -- Row count for each transaction
DECLARE @cnt INT -- Total row count for this run, can be hardcoded
DECLARE @loops INT -- Number of transactions
SET @N = 2000 --must be same value as rows selected in view
SELECT @cnt = 5001 -- Just pretend there are 5001 rows to delete
SET @loops = CEILING(@cnt/@N) -- transactionHow many times to run
WHILE @loops > 0
SET @cnt= @cnt - 2000
SET @loops = @loops - 1 -- decrement @loop
PRINT 'There are ' + CAST(@cnt as varchar(13)) + ' rows left that your algorithm doesn''t delete'