asked on

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

createnetwork

ASKER

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?

Guy Hengel [angelIII / a3]

>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

createnetwork

ASKER

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.

Guy Hengel [angelIII / a3]

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.