How to Allow Users to Control Sort Order in SQL Server Results

It is basic knowledge that the ORDER BY clause will pretty much sort our data using available columns in a SQL Server table in ascending or descending order. But what if we want to allow a user of an application to resort items (rows) in a table in their own desired order where ascending or descending on existing columns would not work? For example, we might want to present a list of items and allow them to re-sort the items in any order they desire. How do we store and retrieve that data in a SQL Server table so that it displays in the order expected? This article will provide one such solution that is relatively easy to implement and use.

The summary of items we will need to code include…

1. Add an integer column named sort to the table that will be re-sorted.
2. Create a new SQL table-valued function that parse the list of sorted items in a delimited string into a corresponding virtual table. This is reusable and available in SQL Server 2005 and later.
3. Write simple SQL that will create and populate a temp table from the virtual table above, then update our target table’s sort column with integer value in the new re-sorted order.

Once these are in place, you will find that you can sort the rows in most any table to match the order the user resorted them in. In this example, let’s assume an application user re-sorts some ToDo list items and we will subsequently update our table so the items may be redisplayed in the new re-sorted order. It might work like this…

1. User re-sorts some items using whatever GUI method you provide and clicks Submit when done.
2. The application code will loop through the re-sorted items and generate a delimited string of key values from those items. The string is then passed to a stored procedure. (This is where our demo begins)
3. The stored procedure will use a table-value function to parse the string and return a virtual table.
4. We create a temporary table and populate it from the virtual table. Then we UPDATE our target table from the temporary table. We can then use ORDER BY sort to retrieve/display the items in the new sort order.

Let’s get started…

First, let’s create a demo table that holds the data we want to the user to sort. This table could be any table with a simple or compound primary key. Line 4 shows the additional integer column we added named sort.  This column will get updated and control the sorting.

[code lang=”sql” toolbar=”0″]
CREATE TABLE [dbo].[demo_sort]
( [id] [INT] NOT NULL,
[todo_item] [VARCHAR](50) NULL,
[sort] [NCHAR](10) NULL,
PRIMARY KEY ( [id] ASC ) )
[/code]

Once the table is created, populate it with at least three rows of data. For example, I added the following data representing a To-Do list. Assume we will allow a user to re-sort this list based on their desired priority. Don’t worry about values for the sort column as that will get filled in when we run our demo.

Next we need to create a Table-Valued function that will accept an incoming delimited string of values and parse that string into a virtual table. You may already have a similar function in your arsenal but if not, this is a great one to have. Once coded, you can use this function again and again for this and other similar purposes.

[code lang=”sql” toolbar=”0″]
ALTER FUNCTION [dbo].[fnStringToTable_demo]
(
@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
[/code]

Execute the code above to create this new table-valued function in your database so we can use in our demo. Now, let’s create a sample stored procedure that shows the code in action.

[code lang=”sql” toolbar=”0″ highlight=”3,8,16,20″]
ALTER PROCEDURE demo_sort_proc
— Add the parameters for the stored procedure here
@p_listofkeys VARCHAR(2000) = NULL
AS
BEGIN
SET NOCOUNT ON;

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

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

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

RETURN

END
[/code]

Note that in line 3 you can see incoming string of delimited keys passed in as @p_listofkeys. Next, in line 8 we create a simple, two-column temporary table. The first column named col1 is an identity column meaning it should be automatically populated with integer values in ascending sequence as rows are inserted. The second column will hold the values parsed from the incoming string. You could name it to match the primary key of our target table. In this example, it is named id to match the name of the primary key in our target table named demo_sort.

In line 16 you see how the new function is called in the FROM clause of our INSERT statement. This inserts rows into our temporary table using the parsed keys from the delimited string. The first value in the string (or virtual table as returned from the function call) will get inserted as the first row into our temporary table along with a new identify value of 1 in the col1 column. The second value will become the second row and so on. As you probably have already guessed by now, the identity values are providing the new sequential sort order values from 1 to the ? number of rows inserted. So the last step is to UPDATE our target table from this this temporary table by JOINing on key value and updating the sort column using the newly created identity keys. Let’s look at this in using our original sample To-Do list above…

Our application user has decided to re-order his priority list so that plan vacation is first, buy groceries is second and that awful chore of mowing the lawn has been moved down to number three in priority!

The application will now pass in the following delimited string of key values based on the new order…

“3, 1, 2”

This will fill our temporary table rows with key values of 3, then 1, then 2 but with sort column values now of 1, 2 and 3 (courtesy of the identify generated values from SQL Server). So after final UPDATE of our demo_sort table, the values will now look like this:

As long as we use ORDER BY sort on our target table, it will display the items in the current sort order which could have been re-sorted by our application user. Give it try and see what you think. Feel free to post any improvements or suggestions.