SQL Server 2005 Update Stored Procedure for Ranking Field

I have a table with a ranking field, so they can have a custom order/rank when being displayed.

e.g.

RANK TABLE
ID | NAME | RANK
1 | Carl | 1
2 | Jon | 2
3 | Fred | 3
4 | Leeroy | 4
5 | Sid | 5

What I need is a stored procedure which takes one parameter which is the ID of the record which is to have its rank moved up...

i.e. You want to move Leeroy up one so you call this procedure with the parameter @ID=4 and it does the following:

1) Finds the record with the next highest rank, i.e. 3 | Fred | 3
Note: I dont want it to just add or minus the rank just in case a record has been deleted, so in theory there could be gaps in the rank numbers and no record with a rank of 3 so it needs to swap with the record with a rank of 2
2) Checks if its already the highest rank which it isnt anyway so continues
3) Swaps the ranks so Leeroy is now 3 and Fred 4 so the table now looks like this when sorted by rank:

RANK TABLE
ID | NAME | RANK
1 | Carl | 1
2 | Jon | 2
4 | Leeroy | 3
3 | Fred | 4
5 | Sid | 5

Im assuming doing this in a stored procedure is a good way of doing it but I dont have much t-SQL experience, if people think im heading the wrong direction and this should be coded in the ASP.NET pages then let me know also but I think a stored procedure would be more tidy.
LVL 2
createnetworkAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
CREATE PROCEDURE dbo.MoveUpRank ( @ID INT )
AS
BEGIN
  DECLARE @rank INT
  SELECT @rank = rank - 1 FROM yourtable WHERE ID = @ID
 
  IF @rank > 1 
  UPDATE yourtable 
      SET rank = CASE WHEN ID = @ID THEN @rank ELSE rank + 1 END
  WHERE ID = @ID 
         OR ID = ( select ID FROM yourtable WHERE rank = @rank )
 
END

Open in new window

0
 
createnetworkAuthor Commented:
Thanks for quick answer and looks good although not tested yet...

But will that work if I have deleted a record and some ranking numbers are missing?

i.e. Fred was deleted then you wanted to move Leeroy up and swap rank numbers with Jon?

RANK TABLE
ID | NAME | RANK
1 | Carl | 1
2 | Jon | 2
4 | Leeroy | 4
5 | Sid | 5

Or would I be better keeping the rank field always correct and if a record is deleted it adds 1 to all records below it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>i.e. Fred was deleted
you should simply ensure that when a record is deleted, it "moves up" the other records:


CREATE PROCEDURE dbo.DeleteItem ( @ID INT )
AS
BEGIN
  DECLARE @rank INT
  SELECT @rank = rank FROM yourtable WHERE ID = @ID
 
  DELETE yourtable WHERE ID = @ID
  
  UPDATE yourtable SET RANK = RANK - 1
  WHERE RANK > @rank
END

Open in new window

0
 
createnetworkAuthor Commented:
Fantastic, great solution and acepted.

On a slightly different note, do you know any books you would recommend on learning how to programme T-SQL better? id been looking at this:

http://www.amazon.co.uk/Beginning-Server-small-Programming-Programmer/dp/0764584332/

Is that going to help me learn things like you just suggested? Thanks again.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I did not learn T-SQL, PL-SQL, etc programming.
I learned, in school, programming methodology, with the first practice in Borland Pascal...
once one knows the basic concepts of how to analyze correctly, and the first think about the entire problems, you can write code very quickly in any language.
of course, some practice is needed in the relevant language to learn the syntax and it's limitations, but the basics are always the same:
* data types, including arrays, collections, self-written classes.
* looping code
* conditional code
* procedural code (ie parameter passing)
* error handling
* and most important: using "abstraction" ie boxing the problem: divide an conquer.
   => don't try to solve all the problems at once. divide the whole into several steps (where you might have several ways), and solve each step individually. if you have several ways, think about the limitations, and find out what way will be most efficient and/or better to use.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.