SQL Apprentice Question
I am trying to create a priority field to organize a list of tasks.
The following 3 fields are being used: EventID (PK, int, not null),
Name (nvarchar(50), not null), pri_ss (int, null).
It would be nice if the code did several things at once:
1. Automitically add items to the list with the max pri_ss number +
(i.e. if you have 50 prioritized items in the list and you add
another one, it adds it as pri_ss = 51)
2. If I change the priority of an item I would like the priority of
all the numbers below it have 1 added to their initial number. (i.e.
if i change item 25 to item 5, i want item 5 to become item 6, and item
6 to become item 7, and so on)
My environment is SQL Server 2000, and i am somewhat fimiliar with.
Any code from SQL experts would be a great help!
Here is an old posting about manipulating such numberings:
Given a motorpool with numbered parking spaces, you want to move the
CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);
Re-arrange the display order based on the parking_space column:
CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER,
= CASE parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
WHERE parking_space BETWEEN @old_parking_space AND @new_parking_space
OR parking_space BETWEEN @new_parking_space AND @old_parking_space;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseMotorpoolGaps()
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);