SQL Stored Procedure to resequence pointer field

Posted on 2011-05-09
Last Modified: 2012-05-11

I am writing an application in VB.Net 2008 which uses SQL Server 2005.  The particular database I Am working with now holds Stock records which has a Primary Key and a Sequence number.  The user may add multiple records and may also remove records from the database which will create gaps in the Sequence.

Since there will or could be gaps I wanted to call a Stored Procedure to re_swquence the records.  I thought I could use a WHILE loop with a variable, but the UPDATE only updates the last record.  Could you show me a Stored Procedure that coulld accomplish this.
Question by:Peter Allen
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >which will create gaps in the Sequence.
    why do you want to avoid the gaps. after all, it's just a number to identify the records ...

    I would not try to do this on "stock" database.
    LVL 9

    Expert Comment

    You can do something like this to repopulate the sequence numbers from the beginning

    UPDAET yt
    SEQ_Column =
     row_number() OVER (ORDER BY SEQ_Column) FROM YourTABLE yt

    Author Comment

    by:Peter Allen
    I tried this code but received the error, "Windowed function can only appear in SELECT and ORDER BY clause".
    LVL 9

    Accepted Solution

    Sorry about the issue, this query would work for you

    coumntoupdate  = bb.rownum
    FROM yourtable yt
    (SELECT pk,row_number() OVER (ORDER BY id) as rownum FROM yourtable) as bb ON =

    Author Closing Comment

    by:Peter Allen
    Thank you very much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now