Traverse a MSSQL table without using CURSOR

Introduction

The original tip avoided the use of cursors, but in exchange, used table row modifications inside of a transaction. This leads to its own share of problems.

Ideally, traversing the rows in a table would require nothing but table reads. This tip shows one way to accomplish this. 

Using the code 

I'll use the same table structure as the original tip:

CREATE TABLE [dbo].[A](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Value] [int] NOT NULL,
    [Flag] [bit] NULL ) 

In addition to traversing an entire table, this method allows us to specify a contiguous subset of the table to traverse if desired.  To begin, set the value of the @Id variable to one less than the first item. This is important; we get the next item in the list at the top of the loop, so this ensures that we don't skip the first item.

Secondly, set the value of @LastID to the id of the last item to be processed.

Code your task after the "--code here" comment, and you're ready to go.  

BEGIN TRY    
 
    BEGIN TRANSACTION
 
    DECLARE @Id INT, @LastID INT    
    SELECT @Id = MIN([Id])-1 FROM A
    SELECT @LastId = MAX([Id])-1 FROM A
        
    WHILE  @Id <= @LastID
        BEGIN      
            --Get the next item in the list 
            SELECT TOP 1 @Id=[Id] FROM A WHERE [Id] > @Id ORDER BY [Id]  
            
             --code here            
             PRINT @id
                              
        END
 
    COMMIT TRANSACTION    
    
END TRY
BEGIN CATCH
 
    ROLLBACK TRANSACTION
    RAISERROR ('Error, Please try again.',16,1)
        
END CATCH

Enjoy!

History

8/3/2012: Original submission.