07-31-2023, 08:15 AM
Marc's answer is good (I'd comment on it if I could work out how to!)
Just thought I'd point out that it may be better to change the loop so the `SELECT` only exists once (in a real case where I needed to do this, the `SELECT` was quite complex, and writing it twice was a risky maintenance issue).
-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1
-- as long as we have customers......
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN
SET @LastCustomerId = @CustomerIDToHandle
-- select the next customer to handle
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerId
ORDER BY CustomerID
IF @CustomerIDToHandle <> @LastCustomerID
BEGIN
-- call your sproc
END
END
Just thought I'd point out that it may be better to change the loop so the `SELECT` only exists once (in a real case where I needed to do this, the `SELECT` was quite complex, and writing it twice was a risky maintenance issue).
-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1
-- as long as we have customers......
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN
SET @LastCustomerId = @CustomerIDToHandle
-- select the next customer to handle
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerId
ORDER BY CustomerID
IF @CustomerIDToHandle <> @LastCustomerID
BEGIN
-- call your sproc
END
END