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.)

Number of rows in city table
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
Loop Through Table Rows With Cursor

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
Loop Through Table Rows Without Cursor

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


2 thoughts on “How to loop through table rows without cursor in SQL Server?”

  1. This is not a good example of while loop as only while loop can not manipulates the data properly and it can give error if row with id is not present because id can be deleted or skipped in actual table which you are comparing in loop.

    Reply
  2. The method of not using a cursor is incorrect and will not work for the general case.
    1: If any CityId has been deleted, you’ll get an error for that row.
    2: If cities are being actively added/deleted by other users you’ll get incorrect results.
    3: If your ID is not an identity / monotonically increasing number you cannot use this method.

    It is almost always better to use a cursor for your operations.

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.