Click here to Skip to main content
Click here to Skip to main content
Alternative Article

Traverse a MSSQL table without using CURSOR

, 3 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ke4vtw

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 3 Aug 2012
Article Copyright 2012 by ke4vtw
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid