SQL code to resequence a grid

This is a short piece of T-SQL code that will resequence the lines in a grid. 

The table that we're resequencing has three unique keys, you'll need to adjust the code to work for your table

 

Related Articles

... and you 'll find more on the SQL (General) Menu

 

-- =============================================
IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'dd_RT010130OPS_RESEQUENCE'
    AND    type = 'P')
    DROP PROCEDURE dd_RT010130OPS_RESEQUENCE
GO
CREATE PROCEDURE dd_RT010130OPS_RESEQUENCE
--  dd_RT010130OPS_RESEQUENCE 'DEFAULT','10','**DA2292-01-219**obsolete**','30', 'UP'
 
@ROUTINGNAME_I char(31),
@RTSEQNUM_I char(11),
@ITEMNMBR char(31),
@LINEITEMSEQ INT,
@DIRECTION VARCHAR(4)       --UP/DOWN
 
AS
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
DECLARE @ORIG AS INT
    SELECT @ORIG = @LINEITEMSEQ
 
    DECLARE @NEW AS INT
     
DECLARE @INTCOUNT AS INT
SELECT @INTCOUNT = COUNT(*)
        FROM RT010130OPS RTO1
        WHERE RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
            AND RTO1.RTSEQNUM_I = @RTSEQNUM_I
            AND RTO1.ITEMNMBR = @ITEMNMBR
IF @INTCOUNT IS NULL BEGIN
    RETURN 
END
 
 
IF @DIRECTION = 'DOWN' BEGIN
 
    SELECT @NEW = MIN(RTO1.LINEITEMSEQ)
        FROM RT010130OPS RTO1
        WHERE RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
            AND RTO1.RTSEQNUM_I = @RTSEQNUM_I
            AND RTO1.ITEMNMBR = @ITEMNMBR
            AND RTO1.LINEITEMSEQ > @ORIG
 
    IF @NEW IS NULL BEGIN
    RETURN 
END
    --dd_RT010130OPS_RESEQUENCE 'DEFAULT','10','**DA2292-01-219**obsolete**','30', 'DOWN'
 
    UPDATE RT010130OPS
    SET LINEITEMSEQ = 0
            FROM RT010130OPS RTO1
        WHERE
        RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
        AND
        RTO1.RTSEQNUM_I = @RTSEQNUM_I
        AND
        RTO1.ITEMNMBR = @ITEMNMBR
        AND
        RTO1.LINEITEMSEQ = @LINEITEMSEQ
 
    UPDATE RT010130OPS
    SET LINEITEMSEQ = @ORIG
            FROM RT010130OPS RTO1
        WHERE
        RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
        AND
        RTO1.RTSEQNUM_I = @RTSEQNUM_I
        AND
        RTO1.ITEMNMBR = @ITEMNMBR
        AND
        RTO1.LINEITEMSEQ = @NEW
 
    UPDATE RT010130OPS
    SET LINEITEMSEQ = @NEW
            FROM RT010130OPS RTO1
        WHERE
        RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
        AND
        RTO1.RTSEQNUM_I = @RTSEQNUM_I
        AND
        RTO1.ITEMNMBR = @ITEMNMBR
        AND
        RTO1.LINEITEMSEQ = 0
 
END
 
IF @DIRECTION = 'UP' BEGIN
     
    SELECT @NEW = MAX(RTO1.LINEITEMSEQ)
        FROM RT010130OPS RTO1
        WHERE RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
            AND RTO1.RTSEQNUM_I = @RTSEQNUM_I
            AND RTO1.ITEMNMBR = @ITEMNMBR
            AND RTO1.LINEITEMSEQ < @ORIG
 
IF @NEW IS NULL BEGIN
    RETURN 
END
     
    --dd_RT010130OPS_RESEQUENCE 'DEFAULT','10','**DA2292-01-219**obsolete**','40', 'UP'
 
    UPDATE RT010130OPS
    SET LINEITEMSEQ = 0
            FROM RT010130OPS RTO1
        WHERE
        RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
        AND
        RTO1.RTSEQNUM_I = @RTSEQNUM_I
        AND
        RTO1.ITEMNMBR = @ITEMNMBR
        AND
        RTO1.LINEITEMSEQ = @LINEITEMSEQ
 
    UPDATE RT010130OPS
    SET LINEITEMSEQ = @ORIG
            FROM RT010130OPS RTO1
        WHERE
        RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
        AND
        RTO1.RTSEQNUM_I = @RTSEQNUM_I
        AND
        RTO1.ITEMNMBR = @ITEMNMBR
        AND
        RTO1.LINEITEMSEQ = @NEW
 
    UPDATE RT010130OPS
    SET LINEITEMSEQ = @NEW
            FROM RT010130OPS RTO1
        WHERE
        RTO1.ROUTINGNAME_I = @ROUTINGNAME_I
        AND
        RTO1.RTSEQNUM_I = @RTSEQNUM_I
        AND
        RTO1.ITEMNMBR = @ITEMNMBR
        AND
        RTO1.LINEITEMSEQ = 0
 
END
 
 
 
GO
 
grant exec on dd_RT010130OPS_RESEQUENCE to public
 
 
--SP_SPS

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences