Solved

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

Posted on 2006-11-09
Medium Priority
945 Views
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
0
Question by:quest_capital
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 17

Expert Comment

ID: 17911150
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
0

Author Comment

ID: 17911429
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
0

LVL 17

Assisted Solution

HuyBD earned 1000 total points
ID: 17911454
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
0

LVL 39

Accepted Solution

appari earned 1000 total points
ID: 17911928
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
0

LVL 1

Expert Comment

ID: 17912292
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?
0

LVL 70

Expert Comment

ID: 17916970
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
0

Author Comment

ID: 17921429
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error â€œConnecting to the Integration Services service on the computer failed with the following error: 'The specified service â€¦
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month15 days, 4 hours left to enroll