SPROC Probs Creating A Non-identity Number Column

Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).

The Jist of my problem... When a user first inserts six pictures, he gets:

|1|
|2|
|3|
|4|
|5|
|6|

All is good. But, say he deletes picture |3|. Now the list order looks like this:

|1|
|2|
<- |3| is removed
|4|
|5|
|6|

And, then he inserts two more pictures, now he his this:

|1|
|2|

|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|

What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.

I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:


Code:


ALTER PROCEDURE dbo.sp_NewPersonalPic

(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)

AS

BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END

BEGIN
SET @photo_number = 1
SELECT

@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END

BEGIN



My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:

photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200

If |3| is removed above, the numbers are reordered based on the time of entry sequence.

Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.

Thanks and sorry for the verbose post.