Click here to Skip to main content
13,349,997 members (48,178 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 bookmarked
Posted 26 Aug 2012

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"


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)
    declare @value  int;
    select top 1 
         @value = value
        ,@id = id
    from SomeTable
    where id > @Id
    order by id;

    -- Do something with @value and other fields.

    print @value;

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)


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


About the Author

John B Oliver
Software Developer (Senior)
Australia Australia
Applications developer specialising in .Net, Sql technologies, web development (TypeScript/JavaScript, MVC, WebAPI, REST), data processing.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
ke4vtw27-Aug-12 5:52
memberke4vtw27-Aug-12 5:52 
GeneralRe: My vote of 5 Pin
John B Oliver27-Aug-12 12:31
memberJohn B Oliver27-Aug-12 12:31 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 27 Aug 2012
Article Copyright 2012 by John B Oliver
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid