J
Jonathan Wood
So... I've implemented a feature to allow users to control the order of
items in one of my tables. This involves adding a new column to my table,
SortOrder.
And I have two stored procedures that deal with this new column: One that
creates a new item with a SortOrder value higher than any that exist. And
another that swap the SortOrder values between two rows.
But now I need help in making these two stored procedures robust in
multi-user situations.
My create routine looks like this:
DECLARE @SortOrder int
SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails WHERE
WorkoutID = @WorkoutID)
IF @SortOrder IS NULL
SELECT @SortOrder = 1
ELSE
SELECT @SortOrder = (@SortOrder + 1)
INSERT INTO mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets, Minutes,
Comments, SortOrder)
VALUES (@WorkoutID, @ActivityID, @Reps, @Sets, @Minutes, @Comments,
@SortOrder)
And my swap routine looks like this:
DECLARE @tmp int
SELECT @tmp = (SELECT SortOrder FROM mc_WorkoutDetails WHERE ID = @ID1)
UPDATE mc_WorkoutDetails SET SortOrder = (SELECT SortOrder FROM
mc_WorkoutDetails WHERE ID = @ID2) WHERE ID = @ID1
UPDATE mc_WorkoutDetails SET SortOrder = @tmp WHERE ID = @ID2
Any suggestions (even suggestions about my existing approach) appreciated!
Thanks.
Jonathan
items in one of my tables. This involves adding a new column to my table,
SortOrder.
And I have two stored procedures that deal with this new column: One that
creates a new item with a SortOrder value higher than any that exist. And
another that swap the SortOrder values between two rows.
But now I need help in making these two stored procedures robust in
multi-user situations.
My create routine looks like this:
DECLARE @SortOrder int
SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails WHERE
WorkoutID = @WorkoutID)
IF @SortOrder IS NULL
SELECT @SortOrder = 1
ELSE
SELECT @SortOrder = (@SortOrder + 1)
INSERT INTO mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets, Minutes,
Comments, SortOrder)
VALUES (@WorkoutID, @ActivityID, @Reps, @Sets, @Minutes, @Comments,
@SortOrder)
And my swap routine looks like this:
DECLARE @tmp int
SELECT @tmp = (SELECT SortOrder FROM mc_WorkoutDetails WHERE ID = @ID1)
UPDATE mc_WorkoutDetails SET SortOrder = (SELECT SortOrder FROM
mc_WorkoutDetails WHERE ID = @ID2) WHERE ID = @ID1
UPDATE mc_WorkoutDetails SET SortOrder = @tmp WHERE ID = @ID2
Any suggestions (even suggestions about my existing approach) appreciated!
Thanks.
Jonathan