?
Solved

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

Posted on 2006-11-09
7
Medium Priority
?
945 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 70

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.

649 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