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:
Populate the working table with the list data and a unique identifier (GUID)
Call the SP, passing in the GUID to identify the list data set to be worked on and the reordering parameters
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