Interesting article. However, the code in the article is basically re-numbering manually instead of taking advantage of the built in Row_Number() functionality.

This seems to be a lot simpler:

(Note: I changed the table to a temp table for easier testing)

-- create the sequence test table

create table #ToDoList

(

todoID int identity(1,1) not null primary key,

PersonID int not null,

todoText nvarchar(200) not null,

todoSequence smallint not null default(0)

)

go

-- populate the sequence test table

insert into #ToDoList(PersonID, todoText, todoSequence)

select 1, 'Task 0', 0 union all

select 1, 'Task 1', 1 union all

select 1, 'Task 2', 2 union all

select 1, 'Task 3', 3 union all

select 1, 'Task 4', 4 union all

select 1, 'Task 5', 5 union all

select 1, 'Task 6', 6 union all

select 1, 'Task 7', 7 union all

select 1, 'Task 8', 8 union all

select 1, 'Task 9', 9

go

SELECT *

FROM #ToDoList

-- resequence tasks

DECLARE @PersonIDBIGINT= 1

, @ToDoIDINT= 1

, @NewSeqINT= 9

-- figure out the direction we are moving in

-- down = when the new sequence variable is greater than the old sequence variable

-- up = when the new sequence variable is less than then the old sequence variable

DECLARE @DownBIT -- 1 means down, 0 means up

SELECT @Down = CASE WHEN todoSequence < @NewSeq -- move down

THEN 1

ELSE 0 -- move up

END

FROM #ToDoList

WHERE todoID = @todoID

;WITH NewSequences AS

(

SELECT *

, ROW_NUMBER() OVER(ORDER BY

CASE WHEN TDL.toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END

-- order by new sequence without ordering, two numbers will have the same seq

, CASE WHEN TDL.toDoID = @ToDoID THEN @Down ELSE ~@Down END)

-- pick with of the two will be first, based on wether we are moving up/down

- 1 -- for 0 based list

AS RowNum

FROM #ToDoList TDL

WHERE PersonID = @PersonID

)

UPDATE TDL

SET TDL.todoSequence = NS.RowNum

FROM #ToDoList TDL

JOIN NewSequences NS ON TDL.ToDoID = NS.ToDoID

SELECT *

FROM #ToDoList

ORDER BY todoSequence

-- clean up

DROP TABLE #ToDoList