• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 963
  • Last Modified:

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

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
quest_capital
Asked:
quest_capital
2 Solutions
 
HuyBDCommented:
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
 
quest_capitalAuthor Commented:
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
 
HuyBDCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
appariCommented:
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
 
YogeshupCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
quest_capitalAuthor Commented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now