?
Solved

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

Posted on 2006-11-09
7
Medium Priority
?
928 Views
Last Modified: 2006-11-18
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
Comment
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
  • Learn & ask questions
7 Comments
 
LVL 17

Expert Comment

by:HuyBD
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

by:quest_capital
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)
but what about the opposite

@New INT = 8,
@Old INT = 4
0
 
LVL 17

Assisted Solution

by:HuyBD
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Accepted Solution

by:
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

by:Yogeshup
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 69

Expert Comment

by:Scott Pletcher
ID: 17916970
ALTER PROCEDURE [dbo].[Events_Rank]      
    @New INT,
    @Old INT
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @rowAdjustment SMALLINT
     SET @rowAdjustment = CASE WHEN @Old > @New THEN 1 ELSE -1 END
UPDATE tbl1
SET t16 = CASE
      WHEN t16 = @Old THEN @New
      ELSE t16 + @rowAdjustment END
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

by:quest_capital
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

764 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