-- 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 @PersonID	BIGINT	= 1
	, @ToDoID		INT		= 1
	, @NewSeq		INT		= 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 @Down		BIT -- 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