I recently had a need to allow users to order all of their different phone numbers. I wanted to make it simple and just provide a text box so the user can type in whatever priority they want the phone number to have. I also wanted it to automatically renumber. So if they already have a number with a priority of 1 and they add or update a number and set it's priority to 1, that one becomes the new #1 and everything else moves down. For this problem, there is a contact table, a phone table and a contactphonexref table that contains the contactid, a phoneid, and a sequence number (priority)
Here is what I came up with in the stored proc to do the reordering....
First thing was to insert or update the contactphonexref record. Here we set the seqno to whatever the user typed in be it 1,3, or 10000. I left this code out because it seems obvious.
Next was to figure out a way to renumber. I wanted to guarantee that the record being inserted or updated got put in the position that the user requested even if it meant bumping others down.
I didn't want to use a cursor, so I thought I would try creating a table variable that has an identity and store the contactphonexrefid and the seqno
declare @t table (newseqno int identity(1,1), contactphonexrefid int, seqno int)
Then I just do an insert into my table variable for all phone records for this contact and order by seqno. As I noted above, I wanted to make sure if the record being inserted or updated had the same sequence number as another record for that contact, the new record got that number. So, I decided I would take the absolute value of the contactphonexrefid in my table variable minus the contactphonexrefid of the record that got inserted or updated. The thinking there is the record being inserted or updated will always have a absolute value of zero and everything else will be greater than zero so it ensures the new record wins.
insert into @t (contactphonexrefid, seqno)
select contactphonexrefid, seqno from contactphonexref (nolock) where contactid = @contactid
order by seqno, abs(@ContactPhonexrefId - contactphonexrefid)
Once this is done, simply update the contactphonexref table from the table variable and set the seqno equal to the identity column which I called newseqno.
update contactphonexref set seqno = t.newseqno
FROM contactphonexref cpa
inner join @t t on t.contactphonexrefid = cpa.contactphonexrefid
where contactid = @contactid
This solution seems to work quite well for me. It is basically an implementation of a stack. I would be interested in hearing other solutions people have used to accomplish this. Special thanks to beej for some insight on this.