Reorder Items in a List
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
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.
Test Code (Data Initialization):
Test Code (List Processing):