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.