-- create the sequence test table

create table dbo.ToDoList
 (
 todoID int identity(1,1) not null primary key,
 PersonId int not null,
 todoText nvarchar(200) not null
 )
go

-- populate the sequence test table
insert into dbo.ToDoList(PersonId, todoText)
select 1, 'Task 0' union all
select 1, 'Task 1' union all
select 1, 'Task 2' union all
select 1, 'Task 3' union all
select 1, 'Task 4' union all
select 1, 'Task 5' union all
select 1, 'Task 6' union all
select 1, 'Task 7' union all
select 1, 'Task 8' union all
select 1, 'Task 9'
go 

insert into dbo.ToDoList(PersonId, todoText)
select 2, 'Task 0' union all
select 2, 'Task 1' union all
select 2, 'Task 2' union all
select 2, 'Task 3' union all
select 2, 'Task 4' 


go
SELECT * FROM dbo.ToDoList

go

alter table dbo.ToDoList
 add todoSequence smallint not null default(0) 
GO

-- now add the initial sequence
update dbo.ToDoList set
 todoSequence = n_seq.newSequence
from dbo.ToDoList tdl
inner join
(
select ROW_NUMBER() over (partition by personID order by personID, todoID) - 1 as newSequence,
 todoID,
 personID
from dbo.ToDoList
) n_seq
 on tdl.todoID = n_seq.todoID AND tdl.personid = n_seq.personid
 GO
 SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence
GO

-- move Line 8 to sequence number 2
exec dbo.[setTodoSequence]
 @personID = 1,
 @todoID = 9,
 @todoSequence = 2
GO
 SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence
GO
exec [dbo].[setTodoSequence]
 @personID = 1,
 @todoID = 1,
 @todoSequence = 9
go
 SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence
GO
DROP TABLE  dbo.ToDoList

CREATE PROCEDURE [dbo].[setTodoSequence]
(
@personID int,
@todoID as int,
@todoSequence as smallint
)
AS
SET NOCOUNT ON;
declare @oldTodoSequence smallint
declare @direction smallint
declare @start smallint
declare @end smallint
-- get the old sequence
set @oldTodoSequence = (select todoSequence from dbo.ToDoList where todoID = @todoID);

SET @direction = Sign(@todosequence - @oldtoDoSequence)
SELECT @start = CASE WHEN @direction = -1 then @todosequence ELSE @oldtodosequence END
	, @end = CASE WHEN @direction = -1 then @oldtodosequence ELSE @todosequence END

update todolist
	set todosequence = CASE WHEN todoid = @todoID THEN @todoSequence ELSE todosequence - @direction END
where todosequence between @start and @end
 and personId = @personid