Wednesday, February 04, 2004

More SQL fun!

I'm posting this for anybody looking for how-tos, but also for my own memory's sake.

Once in a while you have a table that has a field you need to keep sequential. In this instance, we are grouping images, and the groups need to be in a specific order. I have one table that includes these groups. It has a GroupName field, the Photographer's ID, and a GroupSort field, which is used to keep them in order.

So let's say a certain photographer has 5 groups, Getting Ready(1), Formal(2), Ceremony(3), Reception(4), Family(5).
He decides that Formal doesn't need to be there. If you simply deleted Formal through SQL stored procedure or whatnot you would end up with this:
Getting Ready(1)
Ceremony(3)
Reception(4)
Family(5)

Not very sequential. So I did this:
CREATE PROCEDURE Group_Del
@Phtg numeric(18),
@Group nvarchar(50)
AS
DECLARE @NewSort numeric(9), @OldSort numeric(9), @TopSort numeric(9)
--Get the top Sort
SELECT @TopSort = MAX(dbo.Groupings.GroupSort)
FROM dbo.Groupings
WHERE dbo.Groupings.PhotographerID = @Phtg
--Get the current Sort
SELECT @OldSort = dbo.Groupings.GroupSort
FROM dbo.Groupings
WHERE (dbo.Groupings.PhotographerID = @Phtg) AND (dbo.Groupings.GroupName = @Group)
--Delete the group
DELETE FROM dbo.Groupings WHERE (dbo.Groupings.PhotographerID = @Phtg) AND (dbo.Groupings.GroupName = @Group)
--Change sorts if needed..
SELECT @NewSort = @OldSort + 1
WHILE @NewSort <= @TopSort
BEGIN
UPDATE dbo.Groupings SET dbo.Groupings.GroupSort = @NewSort - 1 WHERE (dbo.Groupings.PhotographerID = @Phtg) AND (dbo.Groupings.GroupSort = @NewSort)
SELECT @NewSort = @NewSort + 1
END

So first we get the last sort (@TopSort) and then the sort we are deleting (@OldSort).
We delete the old sort.
Then we start from the next sort (after @OldSort) and work our way to the last sort (@TopSort) shifting them down one at a time.

Now there may be a simpler way to do this, but I haven't found it yet. If you want some more info on this, you know where to find me.

No comments: