# I want to use a stored procedure to reorder records in number sequence (sql 2005)

on
The stored procedure below works.
Note:
t2=title
t16=rank

Let's sat I want title8 to have t16=4 then update all records where t16 > 4 in increments of +1 (see Before / After below.)

Before
Table: tbl1
t1       t16
-------------------
title1     1
title2     2
title3     3
title4     4
title5     5
title6     6
title7     7
title8     8
title9     9
title10   10

After
Table: tbl1
t1       t16
-------------------
title1     1
title2     2
title3     3
title8     4
title4     5
title5     6
title6     7
title7     8
title9     9
title10   10

Problem:
The stored procedure below does this however, what if I need to go the opposite way, let's say we want title6 to have t16=3
There has to be a better way to do this.

ALTER PROCEDURE [dbo].[Events_Rank]
@New INT = 4,
@Old INT = 8

AS
BEGIN
SET NOCOUNT ON
UPDATE tbl1 SET t16 = t16 + 1 WHERE t16 >= @New and t16 <= @Old
UPDATE top (1) tbl1 SET t16 = @new WHERE t16 = @Old + 1
Select t2, t16 from tbl1 order by t16
END
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
ALTER PROCEDURE [dbo].[Events_Rank]
@New INT = 4,
@Old INT = 8

AS
BEGIN
SET NOCOUNT ON
UPDATE tbl1 SET t16 = -1 WHERE t16 =@New
UPDATE tbl1 SET t16 = t16 + 1 WHERE t16 BETWEEN @New and @Old
UPDATE tbl1 SET t16 = @new WHERE t16 = -1
Select t2, t16 from tbl1 order by t16
END

Commented:
HuyBD

one change and it works like mind
UPDATE tbl1 SET t16 = -1 WHERE t16 =@New
to
UPDATE tbl1 SET t16 = -1 WHERE t16 =@Old

This work fine going from High(8) to low(4)

@New INT = 8,
@Old INT = 4
Commented:
Try this

ALTER PROCEDURE [dbo].[Events_Rank]
@New INT = 4,
@Old INT = 8

AS
BEGIN
SET NOCOUNT ON
UPDATE tbl1 SET t16 = -1 WHERE t16 =@New
UPDATE tbl1 SET t16 = t16 + (case when @New<@Old then 1 else -1 end)  WHERE t16 BETWEEN @New and @Old
UPDATE tbl1 SET t16 = @new WHERE t16 = -1
Select t2, t16 from tbl1 order by t16
END
Commented:
Small Correction to HuyBD's post,

i think you need to check the where condition too?

try like this

ALTER PROCEDURE [dbo].[Events_Rank]
@New INT = 4,
@Old INT = 8

AS
BEGIN
SET NOCOUNT ON
UPDATE tbl1 SET t16 = -1 WHERE t16 =@New
if @New<@Old
UPDATE tbl1 SET t16 = t16 + 1 WHERE t16 BETWEEN @New and @Old
else
UPDATE tbl1 SET t16 = t16 + -1 WHERE t16 BETWEEN @Old and @New
UPDATE tbl1 SET t16 = @new WHERE t16 = -1
Select t2, t16 from tbl1 order by t16
END

Commented:
I am a bit confused about the kind of output you want, esp when you say you want "title6 to have t16=3" can you show me the ouput that you are expecting?
Senior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
ALTER PROCEDURE [dbo].[Events_Rank]
@New INT,
@Old INT
AS
BEGIN
SET NOCOUNT ON
SET @rowAdjustment = CASE WHEN @Old > @New THEN 1 ELSE -1 END
UPDATE tbl1
SET t16 = CASE
WHEN t16 = @Old THEN @New
WHERE t16 BETWEEN CASE WHEN @rowAdjustment = 1 THEN @New ELSE @Old END AND
CASE WHEN @rowAdjustment = 1 THEN @Old ELSE @New END
Select t2, t16 from tbl1 order by t16
END

Commented:
this worked

UPDATE tbl1 SET t16 = -1 WHERE t16 = @Old
UPDATE tbl1 SET t16 = (case when @New<@Old then t16 + 1 else t16 -1 end) WHERE t16 BETWEEN (case when @New<@Old then @New else @Old end) and (case when @New<@Old then @Old else @New end)
UPDATE tbl1 SET t16 = @New WHERE t16 = -1

appari was the closes
thx

Do more with