How To Loop Through A SQL Server Table Without Cursors

Cursors in SQL Server are incredibly convenient for iterating through a set of records and processing them one at a time. Unfortunately, you pay the price with an enormous performance hit.

One way around this is to use a while loop. In this example based on the Northwind database, we loop through a result set by setting the loop counter to the minimum ID that we haven't processed yet. The code is only slightly more complicated than using a cursor, but it runs much faster.

One thing to be careful about is to update the loop counter using SET, not SELECT. If the MIN query returns nothing, SET will update @OrderID to NULL. But SELECT would leave @OrderID with whatever value it had previously, resulting in an infinite loop.

In order to prevent locking issues during all this processing, we dump the results of our initial query into a temp table, then loop through the temp table. While this example doesn't show it, you'll want to add an index on OrderID to the temp table to avoid all those table scans.

CREATE TABLE #t (OrderID int, CustomerID nchar(5), EmployeeID int)

INSERT INTO #t (OrderID, CustomerID, EmployeeID)
SELECT OrderID, CustomerID, EmployeeID
FROM Orders
WHERE ShipCountry = 'France'

DECLARE @OrderID int, @CustomerID nchar(5), @EmployeeID int

SELECT @OrderID = MIN(OrderID) FROM #t

WHILE (@OrderID IS NOT NULL)
BEGIN
     SELECT @CustomerID = CustomerID, @EmployeeID = EmployeeID
     FROM #t
     WHERE OrderID = @OrderID

     -- Add your code to process this row using @OrderID, @CustomerID, and @EmployeeID

     SET @OrderID = (SELECT MIN(OrderID)
     FROM #t
     WHERE OrderID > @OrderID)
END

DROP TABLE #t