User Defined ReOrdering in a SQL Server Table

Suppose you want to allow your users to re-order a set of rows in a SQL Server table. Once the user has defined the new order, you need to save that order in SQL Server so that you can re-display the rows in the new order. But how can you accomplish this? You probably added a new column to the target table appropriately named sort. But how do you update this sort column so that it can reflect the new order? Let’s assume we have 3 rows as follows and we have ORDER BY sort in query so that they display as…

and then our user re-orders the list so that its now…

The Sort column values no longer reflect the correct sort order so how we update them so they do? As long as we can generate the values of the ID field in a comma-delimited string as pass to our stored procedure, we can update the sort values so that they reflect the new order. So our generated string of ID’s would look like “2,1,3” and we want our newly updated table to look like…

Note that the Sort values now reflect the new order so we can still use ORDER BY Sort and the rows display as we expected. We can do this by using some simple but clever SQL along with a very useful “table-valued” function that will parse the delimited string into a table of ID values that we can use in a SQL statement. Here’s how.

First, we create a simple temporary table to hold our list of ID values (our primary key for example) in the new order the user has chosen. Note the use of the IDENTITY type will auto generate sequential numbers for us as the rows are inserted.

[sql]
CREATE TABLE #temp1
(
col1 INT IDENTITY(1,1),
id INT
)

[/sql]

Now we use that function I mentioned to populate the temporary table. Using a familiar INSERT INTO and SELECT FROM, the parsed ID (key) values are inserted into the table in the order they were found in the delimited string. We are passing in our list of delimited ID’s in the @p_listofkeys parameter. The second parameter is indicating we want to parse the string using the comma as the delimiter.

[sql]
— insert values from keys past in to procedure
INSERT INTO #temp1 (id)
SELECT value FROM dbo.fnstringtotable(@p_listofkeys,’,’)
[/sql]

Finally, we use our temporary table to UPDATE our target table that we are re-ordering. In this example, that table is named demo_reorder and we joined it directly to the temporary table on the ID column (the same column that holds the ID (pk) values you had in the delimited string. After the UPDATE, the sort column will now hold new consecutive numbers such as 1, 2, 3, etc. in the proper order of the ID values from the string! Now you can ORDER BY sort to see the newly updated sort order.

[sql]
— update the sort field using identity values (1 – ??)
UPDATE dbo.demo_reorder
SET sort = col1
FROM dbo.demo_reorder dr INNER JOIN #temp1 t ON dr.id = t.id
AND ISNULL(sort,0) <> col1
[/sql]

You can use any table-valued function that will return a table value of the delimited string values. I have included one below that will work with this example in case you do not already have one. I am not sure who the original author of this function is.

[sql]

CREATE FUNCTION [dbo].[fnStringToTable]
(
@str varchar(8000), @delim varchar(5)
)
RETURNS @ValueStr TABLE (value varchar(8000))
AS
/******************************************************************************
** Name: fnStringToTable
** Desc: Parses the input parameter string with the delimiter** Parameters:
** Input:
@str – delimited string ex. . 1,2,3 max length is 8000 characters
@delim – delimiter to parse @str ex. ",","-" max length is 5
characters

*******************************************************************************/
BEGIN
DECLARE @str1 VARCHAR(8000),
@len INT,
@endPos INT,
@stPos INT,
@rightLen INT,
@tmpint INT,
@tmpstr VARCHAR(8000)

IF ( @str = NULL
OR LEN(LTRIM(RTRIM(@str))) = 0 )
RETURN

SELECT @str1 = RTRIM(LTRIM(@str))
SELECT @str = @str1
SELECT @len = LEN(@str),
@endPos = 0,
@stPos = -1,
@rightLen = 0

WHILE @stPos <> 0
BEGIN
SELECT @str1 = RIGHT(@str, @len – @rightLen)
SELECT @stPos = CHARINDEX(@delim, @str1)
SELECT @rightLen = @rightLen + @stPos
IF @stPos <> 0
BEGIN
INSERT INTO @ValueStr
( value )
SELECT RTRIM(LTRIM(LEFT(@str1, @stPos – 1)))
END
ELSE
BEGIN
INSERT INTO @ValueStr
( value )
SELECT LTRIM(RTRIM(@str1))
END
END
RETURN
END
[/sql]