WHILE 1 = 1
BEGIN
DELETE TOP (1000)
FROM Sales.MyOrderDetails
WHERE
productid = 12;
IF @@rowcount < 1000
BREAK;
END
The code above uses an infinite loop (WHERE 1 = 1 is always true). In each iteration, a DELETE statement with a TOP option limits the number of affected rows to no more than 1,000 at a time. Then the IF statement checks if the number of affected rows is less than 1,000; in such a case, the last iteration deleted the last chunk of qualifying rows. After the last chunk of rows has been deleted, the code breaks from the loop.
