SQL Server Loop through Table Rows without Cursor

By:   |   Updated: 2023-10-31   |   Comments (10)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > TSQL


Problem

You have always heard that you should avoid cursors in your T-SQL code as a SQL Server best practice, because cursors are detrimental to performance and sometimes cause issues. But sometimes there is a need to loop through the data one row at a time in your SQL queries, so in this SQL tutorial we will look at a comparison of how to do a loop without using cursor.

Solution

We all know that SQL Server, like every relational database allows the user to perform set based operations. Also, as many database vendors do, SQL Server includes a procedural extension which is the T-SQL language. It adds constructs found in procedural languages allowing a more straightforward coding to developers. These constructs were added for a reason and sometimes this is the only approach to the task at hand.

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 use 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 as FOR loops are not available in T-SQL. 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.

Building the Test Environment for Cursors and Loops

To test this, I will use a table with an identity column (CursorTestID), a varchar column (Filler) and a bigint column (RunningTotal).  Here is the SQL statement with the table name, column names and data types:

CREATE TABLE CursorTest
(
   CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- Column Names
   Filler        VARCHAR(4000), -- Data Types
   RunningTotal  BIGINT 
)
GO 

The idea is to loop trough the table rows ordered by the CursorTestID column and update the RunningTotal column with the sum of the CursorTestID column value and the value of the RunningTotal column of the previous row.

But before starting, first we need to generate some test rows with the next Transact-SQL script.

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )
VALUES ( REPLICATE('a', 4000),  0  )
GO 500000

On the script above you will notice that I only used a single insert statement and I took advantage of the batch separator (the GO 500000 command) as a shortcut to execute this insert statement 500000 times. You can read more about this method to repeat batch execution on this tip: Executing a T-SQL batch multiple times using GO.

Simple Example of a Basic Cursor to Loop through Table Rows in SQL Server

Let’s create a cursor to fill the RunningTotal column. Notice on the following example that I declared the cursor with the option FAST_FORWARD. This is done in order to enhance the performance of the cursor because according to Microsoft the FAST_FORWARD argument “Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled”. In other words, we are instructing SQL Server to use a read only cursor that can only move forward and be scrolled from the first to the last row.  Here is the syntax:

DECLARE @CursorTestID INT;
DECLARE @RunningTotal BIGINT = 0;
 
DECLARE CUR_TEST CURSOR FAST_FORWARD FOR
    SELECT CursorTestID RunningTotal
    FROM   CursorTest
    ORDER BY CursorTestID;
 
OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @CursorTestID
 
WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE dbo.CursorTest 
   SET RunningTotal = @RunningTotal + @CursorTestID
   WHERE CursorTestID = @CursorTestID;

   SET @RunningTotal += @CursorTestID

   FETCH NEXT FROM CUR_TEST INTO @CursorTestID
END
CLOSE CUR_TEST
DEALLOCATE CUR_TEST
GO

The next image is a screen capture from SQL Server Management Studio (SSMS) showing the execution of the script above. As you can see, it took three minutes and five seconds to update the 500,000 rows of our test table.

Cursor execution test.

Example of a Basic SQL While Loop to Cycle through Table Rows in SQL Server

Now I will rewrite the previous script avoiding the use of a cursor with a WHILE loop statement. You will notice that it contains a While loop which is almost identical to the statement block in the cursor script where it gets a count of rows with SELECT COUNT and processes row by row with a row number. This is, as I previously said, because even when working with cursors you need to use an iterative control structure.

DECLARE @CursorTestID INT = 1;
DECLARE @RunningTotal BIGINT = 0;
DECLARE @RowCnt BIGINT = 0;

-- Get a count of total rows to process with a SELECT COUNT statement
SELECT @RowCnt = COUNT(0) FROM dbo.CursorTest;
 
WHILE @CursorTestID <= @RowCnt
BEGIN
   UPDATE dbo.CursorTest 
   SET RunningTotal = @RunningTotal  + @CursorTestID
   WHERE CursorTestID = @CursorTestID;

   SET @RunningTotal += @CursorTestID
    
   SET @CursorTestID = @CursorTestID + 1 
 
END

The next image is a screen capture from SSMS of the execution of the loop example above. The result set shows it took less time to run the while loop than the cursor.

Changing the cursor for a While Loop.

Another SQL Server Cursor Example

Let’s take for example the cursor in the tip Standardize SQL Server data with text lookup and replace function. A word of advice, in order to run this code, you should follow the steps in the tip to create the test environment.

CREATE TABLE dbo.Products 
   (
   ProductID INT IDENTITY(1,1),
   ProductName VARCHAR(100)
   )
GO
 
INSERT INTO Products (ProductName) VALUES ('Sir Rodney''s Marmalade') 
INSERT INTO Products (ProductName) VALUES ('Sir Rodney''s Scones') 
INSERT INTO Products (ProductName) VALUES ('Jack''s New England Clam Chowder') 
INSERT INTO Products (ProductName) VALUES ('Louisiana Fiery Hot Pepper Sauce')
INSERT INTO Products (ProductName) VALUES ('Louisiana Hot Spiced Okra ')

CREATE TABLE [dbo].[Synonyms] ( 
    [synonym] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 
    [word] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
) ON [PRIMARY] 
GO 
 
CREATE UNIQUE INDEX [IX_word] ON [dbo].[Synonyms]([word]) ON [PRIMARY] 
GO 
 
INSERT INTO synonyms VALUES ('Jam','Marmalade') 
INSERT INTO synonyms VALUES ('Chowda','Chowder') 
INSERT INTO synonyms VALUES ('Wicked Hot','Hot') 
INSERT INTO synonyms VALUES ('King','Sir')

And here is the cursor code:

SET NOCOUNT ON 
 
DECLARE @word VARCHAR(50),  
    @position INT,  
    @newProductName VARCHAR(500),  
    @oldProductName VARCHAR(500),  
    @newWord VARCHAR(50), 
    @ProductName VARCHAR(500),
    @ProductID INT 
 
DECLARE load_cursor CURSOR FOR 
    SELECT ProductID, ProductName 
    FROM dbo.Products 
 
OPEN load_cursor 
FETCH NEXT FROM load_cursor INTO @ProductID, @ProductName 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @oldProductName = @ProductName 
    SET @ProductName = LTRIM(RTRIM(@ProductName)) 
    SET @newProductName = @ProductName 
    SET @position = CHARINDEX(' ', @ProductName, 1) 
 
    BEGIN 
         WHILE @position > 0 
         BEGIN 
              SET @word = LTRIM(RTRIM(LEFT(@ProductName, @position - 1))) 
              IF @word <> '' 
              BEGIN 
                SELECT @newWord = NULL 
                SELECT @newWord = synonym FROM Synonyms WHERE word = @word  
                IF @newWord IS NOT NULL 
                BEGIN 
                     SET @newProductName = REPLACE(@newProductName, @word, @newWord) 
                END 
              END 
              SET @ProductName = RIGHT(@ProductName, LEN(@ProductName) - @position) 
              SET @position = CHARINDEX(' ', @ProductName, 1) 
         END 
 
         SET @word = @ProductName 
         SELECT @newWord = NULL 
         SELECT @newWord = synonym FROM Synonyms WHERE word = @word 
         IF @newWord IS NOT NULL 
              SET @newProductName = REPLACE(@newProductName, @ProductName, @newWord) 
    END 
 
    IF @oldProductName <> @newProductName 
    BEGIN 
         SELECT @oldProductName AS OldProductName, @newProductName AS NewProductName
         --UPDATE dbo.Products SET ProductName = @newProductName WHERE ProductID = @ProductID  
    END 
 
    FETCH NEXT FROM load_cursor INTO @ProductID, @ProductName 
END 
 
CLOSE load_cursor 
DEALLOCATE load_cursor 
GO

If we dissect this code, we can see that there is one cursor that goes through the table products which I copied below.

DECLARE load_cursor CURSOR FOR 
    SELECT ProductID, ProductName 
    FROM dbo.Products 

SQL Server Cursor Example Converted to a While Loop

In order to replace this cursor with a WHILE LOOP, we need to create a temporary table to implement a tally table. For all of you who don’t know what a tally table is, we can define it as a table that contains a pair of columns consisting of a key and its value. In our particular case we will use a sequential integer key starting from 1, so we can use it as an iterator. This key will be associated to a ProductID from the Products table.

CREATE TABLE #TallyTable ( Iterator INT IDENTITY(1, 1), ProductID INT );
 
INSERT INTO #TallyTable ( ProductID )
SELECT ProductID FROM dbo.Products;

At first, since the Products table has the ProductID key defined as an identity you may be tempted to bypass this step, but you have to consider that in a real case a row could have been deleted, therefore you won’t be able to use the identity column as an iterator. Additionally, a row can be deleted while we are running our code and it could lead to execution errors. To avoid this, we are going to add a TRY-CATCH block. I will go into this further on.

Before starting the WHILE loop, we need to set its start and stop condition. For this matter I added two new integer variables named @Iterator and @MaxIterator. The @MaxIterator variable is used to keep the number of items in the #TallyTable table and we set its value only once before starting the loop. The @Iterator variable is initialized to 1, as we defined it as the starting number on the sequence and we are going to increment its value at each iteration.

SET NOCOUNT ON 
 
DECLARE @word VARCHAR(50),  
    @position INT,  
    @newProductName VARCHAR(500),  
    @oldProductName VARCHAR(500),  
    @newWord VARCHAR(50), 
    @ProductName VARCHAR(500),
    @ProductID INT,
    @MaxIterator INT,
    @Iterator INT  
 
CREATE TABLE #TallyTable ( Iterator INT IDENTITY(1, 1), ProductID INT );
   
INSERT INTO #TallyTable ( ProductID )
SELECT ProductID FROM   dbo.Products;
 
SELECT @MaxIterator = MAX(Iterator), @Iterator = 1
FROM   #TallyTable;
 
SELECT @ProductID = ProductID
FROM   #TallyTable
WHERE  Iterator = @Iterator;
 
SELECT @ProductName = ProductName 
FROM dbo.Products 
WHERE ProductID = @ProductID -- WHERE ID equals a variable
 
BEGIN TRY
   WHILE @Iterator <= @MaxIterator 
   BEGIN 
      SET @oldProductName = @ProductName; 
      SET @ProductName = LTRIM(RTRIM(@ProductName)); 
      SET @newProductName = @ProductName; 
      SET @position = CHARINDEX(' ', @ProductName, 1); 
 
      BEGIN 
          WHILE @position > 0 
          BEGIN 
              SET @word = LTRIM(RTRIM(LEFT(@ProductName, @position - 1))); 
              IF @word <> '' 
              BEGIN 
 
               SELECT @newWord = NULL; 
               SELECT @newWord = synonym FROM Synonyms WHERE word = @word;  
               IF @newWord IS NOT NULL 
               BEGIN 
                   SET @newProductName = REPLACE(@newProductName, @word, @newWord); 
               END; 
              END; 
              SET @ProductName = RIGHT(@ProductName, LEN(@ProductName) - @position); 
              SET @position = CHARINDEX(' ', @ProductName, 1); 
          END; 
 
          SET @word = @ProductName; 
          SELECT @newWord = NULL; 
          SELECT @newWord = synonym FROM Synonyms WHERE word = @word; 
          IF @newWord IS NOT NULL 
              SET @newProductName = REPLACE(@newProductName, @ProductName, @newWord); 
      END; 
 
      IF @oldProductName <> @newProductName 
      BEGIN 
          SELECT @oldProductName AS OldProductName, @newProductName AS NewProductName;
          --UPDATE dbo.Products SET ProductName = @newProductName WHERE ProductID = @ProductID  
      END; 
 
      SET @Iterator = @Iterator +1;
 
      SELECT @ProductID = ProductID
      FROM   #TallyTable
      WHERE  Iterator = @Iterator;
 
      SELECT @ProductName = ProductName 
      FROM dbo.Products 
      WHERE ProductID = @ProductID;
   END; 
END TRY
 
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber ,
       ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
 
DROP TABLE #TallyTable
GO
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-10-31

Comments For This Article




Wednesday, July 10, 2024 - 12:21:51 PM - Ronald Duncan Back To Top (92381)
For the running total problem sum over is a better solution.
select id,
somedate,
somevalue,
sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable

I generally find cursors work best for solving problems where iterating through the result set is the best solution. e.g. combining values from multiple rows based on complex conditions, and needing to know the value from X rows forward or backward. These sort of problems are best solved in a scripting language like AWK or python, but sometimes it needs to be done in SQL.

Monday, June 26, 2023 - 2:40:28 PM - Ron Klimaszewski Back To Top (91343)
You can streamline so that you do not need to pre-define the table or query the table to retrieve the @MaxIterator value:

SELECT
IDENTITY(INT,1,1) as Iterator
, ProductID
INTO #TallyTable
FROM dbo.Products;

SELECT @MaxIterator = @@ROWCOUNT, @Iterator = 1;

Thursday, March 3, 2022 - 1:25:15 AM - mayur chaaudhari Back To Top (89850)
Hi can i view one row at a time till all rows are viewed on execution of statement

Tuesday, July 20, 2021 - 10:21:42 PM - Naveen kumar Back To Top (89032)
Awesome article , Thanks Daniel

Tuesday, July 6, 2021 - 11:44:54 AM - Aaron Bertrand Back To Top (88952)
https://sqlperformance.com/2019/10/t-sql-queries/overlooked-t-sql-gems#cursor

Tuesday, July 6, 2021 - 11:44:38 AM - Aaron Bertrand Back To Top (88951)
(Also I would check out Itzik's post on squeezing even a little more performance out of a cursor using a cursor variable, which doesn't have the same overhead as a default cursor and is more forgiving if you forget close/deallocate etc.)

Tuesday, July 6, 2021 - 11:37:22 AM - Aaron Bertrand Back To Top (88950)
>> While loops are faster than cursors.

Do you have any reference for this statement? There might be some truth to this for a *default* cursor and a perfectly optimized while loop, but I don't agree this is generally true.

https://sqlblog.org/2012/01/26/bad-habits-to-kick-thinking-a-while-loop-isnt-a-cursor
https://sqlperformance.com/2012/09/t-sql-queries/cursor-options
https://sqlperformance.com/2015/09/t-sql-queries/cursor-options-2

Wednesday, April 14, 2021 - 6:09:32 PM - Chip Owen Back To Top (88542)
This is great. I have used cursors and while loops indescriminately, just to get the job done, based on the examples I plucked out of prior efforts. This helps me ascertain why one may be better than the other. I appreciate your effort walking through the differences.

Friday, November 27, 2020 - 4:07:11 PM - Erwin Traas Back To Top (87859)
I wonder how I can test if a temporary has records or not. Is this possible?

regards.

Erwin

Monday, March 2, 2020 - 8:44:29 AM - Ibi Back To Top (84878)
***GJR - This looks like this might be a Crystal Reports error. Also doesn't belong with this tip ***

SELECT 

xai.ward,pb.forenames||' '||pb.surname AS patient,

xmo.protocol_description,

xmo.drug_description,

xmod.primary_dose||' '||xmo.primary_dose_description AS dose,

xmo.route,

xmod.frequency_description,

xmod.change_start_date,

xmod.change_start_time,

xmod.stop_date,

xmod.stop_time,

xmod.ordered_date,

xmod.ordered_time,

xmov.verify_date,

xmov.verify_time,

xmov.status

FROM JAC_Super.x_active_inpatients xai

INNER JOIN JAC_Super.x_active_spells xas ON xai.lnkpid=xas.lnkpid

INNER JOIN JAC_Super.x_med_spell_orders xmso ON xas.lnkpid=xmso.lnkpid AND xas.current_spell=xmso.lnkspell

INNER JOIN JAC_Super.x_med_orders xmo ON xmso.lnkpid=xmo.lnkpid AND xmso.order_id_start>=xmo.lnkordid AND xmso.order_id_end<=xmo.lnkordid

INNER JOIN 

(SELECT * 

FROM JAC_Super.x_med_order_doses 

GROUP BY lnkpid,lnkordid 

HAVING MIN(change_start_date*86400+ change_start_time)=change_start_date*86400+ change_start_time) xmod 

ON xmo.lnkpid=xmod.lnkpid AND xmo.lnkordid=xmod.lnkordid

LEFT JOIN 

(SELECT

lnkpid,lnkordid,verify_date,verify_time,status,change_start_date,change_start_time

    FROM JAC_Super.x_med_order_verify

    GROUP BY lnkpid,lnkordid,change_start_date,change_start_time

    HAVING MIN(verify_date*86400+verify_time)=verify_date*86400+verify_time) xmov 

    ON xmo.lnkpid=xmov.lnkpid AND xmo.lnkordid=xmov.lnkordid AND xmod.change_start_date=xmov.change_start_date AND xmod.change_start_time=xmov.change_start_time

INNER JOIN JAC.patient_basic pb ON xai.lnkpid=pb.lnkpid

WHERE (xmov.status='V' OR xmov.status IS NULL) 

 

this is the error am getting. any idea?

 

"Fail to retrieve data from the database.

[Location:<ServerLoop- Query Fetch>]

[Database Vendor Code: 400]

 

this report works for my colleague who are on different company

 















get free sql tips
agree to terms