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.
- More about WHILE LOOP at Microsoft Docs.
- More about CURSOR at Microsoft Docs.
2 thoughts on “How to loop through table rows without cursor in SQL Server?”
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.
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.