Custom Sequence Numbering –(Merge Solution)

I have come across one question at ASP.NET forum and I provided one solution to the question related to resorting sequence.

http://forums.asp.net/t/1913208.aspx/2/10?Reorder+the+column+order+while+update+.

James Skipwith posted one article at sqlservercentral.com http://www.sqlservercentral.com/articles/T-SQL/72068/.

I would like to post my solution here with table DDL modification to work with James’s question.

--Create Stored Procedure

CREATE PROCEDURE [dbo].[setTodoSequence]

(
@personID int,
@todoID as int=63,
@todoSequenceTarget as smallint
)
AS
SET NOCOUNT ON;

declare @todoSequenceExising int

Select @todoSequenceExising=( Select todoSequence  From ToDoList WHERE todoID=@todoid);


begin tran;
BEGIN TRY
--SQL Server 2008 or above

MERGE ToDoList AS t

Using (

SELECT todoID,
CASE WHEN @todoSequenceExising > @todoSequenceTarget

THEN 
 
     CASE
		 WHEN  (todoSequence  >= @todoSequenceTarget AND todoSequence  < @todoSequenceExising) THEN todoSequence +1
		 WHEN  (todoSequence  > @todoSequenceExising  or todoSequence  < @todoSequenceTarget) THEN todoSequence 
		 WHEN todoSequence  = @todoSequenceExising THEN @todoSequenceTarget
     END
 WHEN @todoSequenceExising < @todoSequenceTarget

THEN 
	 CASE
		 WHEN todoSequence  > @todoSequenceExising AND todoSequence  <= @todoSequenceTarget THEN todoSequence -1
		 WHEN   (todoSequence  < @todoSequenceExising  or todoSequence  > @todoSequenceTarget) THEN todoSequence 
		 WHEN  @todoSequenceExising < @todoSequenceTarget AND todoSequence  = @todoSequenceExising THEN @todoSequenceTarget
    END

	ELSE 
	todoSequence  


END
							   AS todoSequence 
       FROM   ToDoList 
	   WHERE personID = @personID) AS src (todoID,  todoSequence )
ON ( src.todoID = t.todoID )
WHEN Matched THEN
  UPDATE SET   t.todoSequence  = src.todoSequence   ;

  
 COMMIT ;

END TRY
BEGIN CATCH
 SELECT @@error;
 ROLLBACK ;
END CATCH

GO

--DDL for table ToDoList
If Exists (  SELECT *	FROM sys.tables	JOIN sys.schemas
	  ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'toDoList' )
   Begin
   Drop table ToDoList
   End

create table ToDoList (todoID int primary key, personId int, todoText varchar(50), todoSequence  int)

insert into ToDoList values (61,1,'colour_thumbnails',1)
,(66,1,'sent_to_print', 2)
,(63,1,'page_approved', 3)
,(60,1,'line_drawing', 4)
,(64,1,'photo_taken',5)
,(65,1,'photo_approved',6)
,(55,1,'line_drawing_photo',7)


---Test From Here

--Before run stored procedure [dbo].[setTodoSequence]
select * from ToDoList
order by todoSequence ;


--Change parameters here!!!!!!!!!!

EXEC	[dbo].[setTodoSequence]
		@personID = 1,
		@todoID = 55,
		@todoSequenceTarget = 6


 
 --After run stored procedure [dbo].[setTodoSequence]
select * from ToDoList
order by todoSequence 

I have read Goldie Lesser’s comments with a Row_number() solution and you can find a newer solution with Row_number and Merge at this follow-up post:
https://jingyangli.wordpress.com/2013/06/17/custom-sequence-numbering-2-row_number-and-merge-solution/

Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Buck Woody posted this: Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Also, I found another link is worth reading about peer-to-peer replication from Timothy Khouri:

Another post I read related to Replication:

Swap a Row Within a Table with MERGE

--DDL for table test
create table test (taskID int,	milestone varchar(50),	sortOrder int)

insert into test values (2861,'colour_thumbnails',1)
,(2866,'sent_to_print',	2)
,(2863,'page_approved', 3)
,(2860,'line_drawing', 4)
,(2864,'photo_taken',5)
,(2865,'photo_approved',6)
,(2859,'line_drawing_photo',7)

select * from test;
--Define the sortorder to be replaced
declare @sortOrder1 int=3, @sortOrder2 int=5


--SQL Server 2008 or above

MERGE test AS t
Using (SELECT taskID,milestone,CASE
                                 WHEN sortOrder = @sortOrder2 THEN @sortOrder1
                                 WHEN sortOrder = @sortOrder1 THEN @sortOrder2
                               END AS sortOrder
       FROM   test) AS src (taskID, milestone, sortOrder)
ON ( src.sortOrder = t.sortOrder )
WHEN Matched THEN
  UPDATE SET t.taskID = src.taskID,t.milestone = src.milestone,t.sortOrder = src.sortOrder; 



select * from test

drop table test