Using a While Loop Instead of Cursors in SQL Server

If you have ever worked with cursors, you may find this title a bit confusing because after all, cursors uses while constructs to iterate between rows. But besides that, I want to show you that in some circumstances when we use a cursor to iterate over a set of rows we can change it to a while loop. In such cases, the only challenge will be to choose a proper exit condition.

Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server

Not everything is wrong with cursors, they also have some advantages over other looping techniques.

  • Cursors are updatable: When you create a cursor, you use a query to define it using the DECLARE CURSOR instruction. By using the UPDATE option in the cursor creation statement, you can update the columns within the cursor.
  • You can move forward and backward in a cursor: By using the SCROLL option in the DECLARE CURSOR statement you can navigate across the cursor records in both directions with the fetch options FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE. Keep in mind that the SCROLL option is incompatible with the FORWARD_ONLY and FAST_FORWARD options.
  • Cursors can be passed to stored procedures: If you use the GLOBAL option to create a cursor, it can be used in any stored procedure or batch executed in the same connection. This allows you to use cursors on nested stored procedures.
  • Cursors have a lot of different options: With cursors you have the chance to use different options that affects how they will behave in regards to locking.
  • Cursors don’t need a condition: By using cursors, you are handling a set of rows as a record. This allows you to move across the cursor without the need of having a Boolean condition. For example, you can create a cursor with the name of the databases residing on a SQL Server instance without the need of a surrogate key to work as a test condition like on a WHILE loop.

There are also some negative aspects that you should be aware when using cursors instead of other looping options.

  • If you use global cursors in your code you are taking the risk of facing errors due to a cursor being closed by some stored procedure nested in your code.
  • Usually cursors have less performance than an equivalent loop using a WHILE loop or CTE.

Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server

There are also benefits to use a WHILE loop compared to a cursor.

  • While loops are faster than cursors.
  • While loops use less locks than cursors.
  • Less usage of Tempdb: While loops don’t create a copy of data in tempdb as a cursor does. Remember that cursors, depending on the options you use to create them can cause the temp tables to be created.

The next list details the negative aspects of WHILE loops.

  • Moving forward or backward is complex: To move forward or backward in a loop you need to dynamically change the iteration condition inside the loop. This requires extra care; otherwise you can end up in an infinite loop.
  • The risk of an infinite loop: Compared to a cursor, you don’t have a fixed set of data to loop (i.e. the data returned by the SELECT statement in the cursor declaration), instead when using a WHILE loop you have to define a boundary with an expression that is evaluated to true or false.