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/

Advertisements

One Comment on “Custom Sequence Numbering –(Merge Solution)”


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s