Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Tagged as

Traverse an MSSQL Table without using CURSOR

, 26 Aug 2012
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "Traverse a MSSQL table without using CURSOR"

Introduction

The problem with the above tip is that it involves the modification of the underlying table schema. A better solution would be to run the traversal over the designed table schema, utilising the primary key value as a traversal key.

The following tip requires a sortable primary key.

Table Creation

if object_id('dbo.SomeTable', 'U') is not null
    drop table dbo.SomeTable;
    
create table dbo.SomeTable
(
     id     int identity(1, 1) not null primary key  - Used as Traversal Key
    ,Value  int not null
); 

Traversal Code

declare @id int = 0;  -- Initialised to a value smaller than the initial traversal key value.

while exists (select top 1 id from SomeTable where id > @id)
begin
    declare @value  int;
    
    select top 1 
         @value = value
        --,OtherFields
        ,@id = id
    from SomeTable
    where id > @Id
    order by id;

    -- Do something with @value and other fields.

    print @value;
end 

The benefit of this means you are no longer modifying the underlying schema of the table, and because of the sort operation, you can use any field as the traversal key.

The only requirement is that the traversal key must be unique.

Simple Table Population Code

insert into SomeTable (Value)
values
 (4) 
,(8)
,(1) 

License

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

Share

About the Author

John B Oliver
Software Developer (Senior) DesignStreaks
Australia Australia
Applications developer specialising in .Net & Sql technologies and dabble in web development, data processing and Web.API/MVC.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberke4vtw27-Aug-12 4:52 
GeneralRe: My vote of 5 PinmemberJohn B Oliver27-Aug-12 11:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 27 Aug 2012
Article Copyright 2012 by John B Oliver
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid