How to loop through table rows without cursor in SQL Server?

When writing business logic in a stored procedure, sometimes you may need to process the tables row-by-row. You might have used CURSORs to perform such tow-by-row operations. However, I do not prefer to use cursors as they are slow and will impact performance. As a good practice I always try not to use cursors in my sql code. But, how to loop through table rows without a cursor? Let’s see with a simple example.

Example of Cursor

In this example, I have used a cursor to loop through 134,460 records from the table called Cities. Then for this illustration I have printed the data from the individual records. (Of course you do not need a cursor for this operation. However this is just for illustration.)

DECLARE @CityID INT;
DECLARE @CityName NVARCHAR(50);
DECLARE @Population BIGINT;

DECLARE CUR_TEST CURSOR FAST_FORWARD FOR
   SELECT CityID FROM Cities

OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @CityID
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @CityName = CityName, 
      @Population = Population 
      FROM Cities WHERE CityID = @CityID

   PRINT 'Population of ' + @CityName + ' is ' + 
      CAST(@Population AS NVARCHAR)

   FETCH NEXT FROM CUR_TEST INTO @CityID
END

CLOSE CUR_TEST
DEALLOCATE CUR_TEST
GO

The time taken by the fast forward cursor to complete the operation is 8 seconds.

Converting Cursor to While Loop

Now, let us convert the above example to WHILE loop. To convert a cursor to while loop, first you have to find the total number of rows in the table. Then you have to iterate through the table rows using WHILE control-of-flow element till the total row count is reached. Here is how the above example is converted to while loop:

DECLARE @RowCnt INT;
DECLARE @CityID INT = 1;
DECLARE @CityName NVARCHAR(50);
DECLARE @Population BIGINT;

SELECT @RowCnt = COUNT(*) FROM Cities;

WHILE @CityID <= @RowCnt
BEGIN
   SELECT @CityName = CityName, 
      @Population = Population 
      FROM Cities WHERE CityID = @CityID

   PRINT 'Population of ' + @CityName + ' is ' + 
      CAST(@Population AS NVARCHAR)

   SET @CityID += 1
END
GO

If you notice, the while loop took 6 second to complete the operation. When you are working with complex operations, you will notice the while loop is much faster than the fast forward cursor.

Reference

• More about WHILE LOOP at Microsoft Docs.
• More about CURSOR at Microsoft Docs.