Reorder Items in a List

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: Unknown

Description

Several times over the past 10 years, I've been involved with writing code to manipulate lists.

One notable occasion was for a large Canadian online DVD rental company for which this formed a core part of their web site.

The code written on that occasion was a little less than elegant, but it worked.

I stumbled across this piece of code which was originally for manipulating a todo list and I've tried to genericise it to cope with any enumerated list.

Initially i thought i may be able to make use of the ability to pass a table as a paraemter to an SP, but these table parameters must first be declared as

a user-defined datatype, and are only passable as read-only parameters - pretty useless when the idea is to reorder and return the contents.

Finally I had to settle on retaining the idea used in the original code, which was to utilise a working table that was usable concurrently:

1. Populate the working table with the list data and a unique identifier (GUID)
2. Call the SP, passing in the GUID to identify the list data set to be worked on and the reordering parameters
3. Consume the results once the SP has completed, again using the GUID to identify the list data set

I will only produce 2 pieces of test code - one to populate the list data (with multiple lists)

and the second to test some list reordering.

This will demonstrate that only data from the specified list will be reordered, thus making the SP suitable for concurrent use.

Some assumptions here:

- The list item is a VARCHAR(MAX) data type - conversion to and from this data type (if required) will be the responsibility of the calling process
- The List Item has been included for clarity. It really is superfluous as the List PK alone is used in the reordering
- List PK is a single column and is an integer
- The list order is an integer sequence with no gaps. Must be one-based ie 1,2,3,4,5...

I've attempted to make sure that the code can be fairly easily adapted for most list purposes, but I'd appreciate any feedback.

Code

Stored Procedure:

DROP PROCEDURE dbo.usp_ReorderListItems

GO

CREATE PROCEDURE dbo.usp_ReorderListItems

(

@ListGUID UNIQUEIDENTIFIER, -- to allow concurrent use of the list table, this identifies the list to be processed

@ListPK INT, -- PK to uniquely identify the item within the specified list that is to be reordered

@TargetListSequenceID INT -- The new sequence ID that the item is to be changed to

)

AS

/**************************************************************************

Purpose: To change the position of an item in a list, reordering the

remaimning items in a list.

This is done using a single update command and without the use

of a cursor, CTE or loop.

Author: Unknown

History: ?? - Intial Issue

19 Jul 2012 - ChillyDBA - a slight reworking to make it more usable concurrently, plus

extensive renaming to more generic names (the original code was aimed at a ToDo list)

***************************************************************************/

DECLARE @OldListSequenceID INT

-- the the id of the item to be updated

-- this becomes the old value that is used in the update code

SELECT @OldListSequenceID = ListSequenceID

FROM ListData_Working

WHERE ListGUID = @ListGUID

AND ListPK = @ListPK

SELECT @OldListSequenceID, @ListGUID, @ListPK

UPDATE lw2

SET lw2.ListSequenceID = ns.NewListSequenceID

FROM ListData_Working lw2

INNER JOIN

(

SELECT

lw3.ListPK,

lw3.ListSequenceID,

ROW_NUMBER() OVER

(ORDER BY

(

CASE

WHEN @TargetListSequenceID > @OldListSequenceID THEN -- moving down as the new sequence number is higher than the old one

CASE

WHEN lw3.ListSequenceID = @OldListSequenceID THEN @TargetListSequenceID + 2

WHEN (lw3.ListSequenceID <= @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID -- less than new seq so leave alone

WHEN (lw3.ListSequenceID > @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID + 2

END

WHEN @TargetListSequenceID < @OldListSequenceID THEN -- moving up as the new sequence number is less than the old one

CASE

WHEN lw3.ListSequenceID = @OldListSequenceID THEN @TargetListSequenceID + 1

WHEN (lw3.ListSequenceID >= @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID + 2 -- greater than new seq so leave alone

WHEN (lw3.ListSequenceID < @TargetListSequenceID AND lw3.ListSequenceID <> @OldListSequenceID) THEN lw3.ListSequenceID

END

ELSE lw3.ListSequenceID -- not moving (the sequence number is the same as it was before)

END

)

) AS NewListSequenceID

FROM ListData_Working lw3

WHERE ListGUID = @ListGUID

) ns

ON lw2.ListPK = ns.ListPK

WHERE lw2.ListGUID = @ListGUID

Test Code (Data Initialization):

/**********************************************************

Test Code #1

Create the list to be managed

-----------------------------------------

**********************************************************/

DROP TABLE ListData_Working

GO

CREATE TABLE ListData_Working

(

ListGUID UNIQUEIDENTIFIER,

ListPK INT,

ListSequenceID INT,

ListItem VARCHAR(MAX)

)

DECLARE @GUID UNIQUEIDENTIFIER

SELECT @GUID = 'E62516A7-5C7C-4DA0-9BB9-6E02523AEDAD' --NEWID()

INSERT ListData_Working (ListGUID, ListPK, ListSequenceID, ListItem)

SELECT @GUID, 10001, 1, 'List 0 Item 1'

UNION

SELECT @GUID, 10002, 2, 'List 0 Item 2'

UNION

SELECT @GUID, 10003, 3, 'List 0 Item 3'

UNION

SELECT @GUID, 10004, 4, 'List 0 Item 4'

UNION

SELECT @GUID, 10004, 5, 'List 0 Item 5'

UNION

SELECT @GUID, 10004, 6, 'List 0 Item 6'

UNION

SELECT @GUID, 10004, 7, 'List 0 Item 7'

SELECT @GUID = '9A55F4E1-8AE8-4399-B5DB-B19352688D77' --NEWID()

INSERT ListData_Working (ListGUID, ListPK, ListSequenceID, ListItem)

SELECT @GUID, 10001, 1, 'List 1 Item 1'

UNION

SELECT @GUID, 10002, 2, 'List 1 Item 2'

UNION

SELECT @GUID, 10003, 3, 'List 1 Item 3'

UNION

SELECT @GUID, 10004, 4, 'List 1 Item 4'

SELECT @GUID = '7A30986C-354E-4201-B1BE-CE34FB304C16' --NEWID()

INSERT ListData_Working (ListGUID, ListPK, ListSequenceID, ListItem)

SELECT @GUID, 20001, 1, 'List 2 Item 1'

UNION

SELECT @GUID, 20002, 2, 'List 2 Item 2'

UNION

SELECT @GUID, 20003, 3, 'List 2 Item 3'

UNION

SELECT @GUID, 20004, 4, 'List 2 Item 4'

UNION

SELECT @GUID, 20005, 5, 'List 2 Item 5'

UNION

SELECT @GUID, 20006, 6, 'List 2 Item 6'

SELECT @GUID = '0536658B-1677-43AC-ADF5-CEF0A2E30BF1' --NEWID()

INSERT ListData_Working (ListGUID, ListPK, ListSequenceID, ListItem)

SELECT @GUID, 30001, 1, 'List 3 Item 1'

UNION

SELECT @GUID, 30002, 2, 'List 3 Item 2'

UNION

SELECT @GUID, 30003, 3, 'List 3 Item 3'

UNION

SELECT @GUID, 30004, 4, 'List 3 Item 4'

UNION

SELECT @GUID, 30005, 5, 'List 3 Item 5'

SELECT *

FROM ListData_Working

ORDER BY ListGUID, ListSequenceID

Test Code (List Processing):

/**********************************************************

Test Code #2

Reorder an item upwards

-----------------------------------------

**********************************************************/

SELECT *

FROM ListData_Working

ORDER BY ListGUID, ListSequenceID

EXEC dbo.usp_ReorderListItems

@ListGUID = '0536658B-1677-43AC-ADF5-CEF0A2E30BF1',

@ListPK = 30004,

@TargetListSequenceID = 2

SELECT *

FROM ListData_Working

ORDER BY ListGUID, ListSequenceID