Solved

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

Posted on 2006-11-09
7
875 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 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