Solved

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

Posted on 2006-11-09
7
913 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 250 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 39

Accepted Solution

by:
appari earned 250 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

Industry Leaders: 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!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

690 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