T-SQL - Enabling Dynamic Sorting using CASE
Let’s visualize you have a database table as below:
Column Name | EmployeeID | Name | IC | DateOfBirth |
Data Type | Numeric(28,2) | Varchar(100) | Varchar(50) | DateTime |
Then you want to have a GridView in the user interface that looks like this:
Header Name | No | Employee Identification Card No | Name | Identification Card No | Date Of Birth | Detail |
DataField | (auto gen) | EmployeeID | Name | IC | DateOfBirth | Click Me |
SortExpression | (n/a) | EmployeeID | Name | IC | DateOfBirth | Click Me |
As you can see, I enable 4 columns to be sortable.
I am relying on my stored procedures to achieve the sorting. I know you can pass the results from this table into a DataView and sort it.
So if you are using store procedure to achieve this, there are 2 ways to achieve this either using dynamic SQL or CASE statement. If you know a better approach, let me know.
I try not to use dynamic SQL here as part of best practices that my company is enforcing. But actually there is a limitation to CASE statement too. For each CASE, you have to use the same data type. So it is best to convert all to string. Why? From the 4 columns, 2 of them are already strings. J
Let’s see some syntax here:
SELECT
ROW_NUMBER()
OVER
(
ORDER BY
CASE WHEN @OrderByColumn='EmployeeID' THEN [EmployeeID]
WHEN @OrderByColumn='Name' THEN [Name]
WHEN @OrderByColumn='IC' THEN [IC]
WHEN @OrderByColumn='DateOfBirth' THEN [DateOfBirth]
END ASC
) AS Row,
[EmployeeID],
[Name],
[IC],
[DateOfBirth]
FROM
[dbo].[tbEmployee]
I am leveraging the new syntax ROW_NUMBER in SQL Server 2005, so it is encouraged you place the ORDER BY and CASE statement with the ROW_NUMBER syntax. Well I have reasons why I do this? Basically in all my stored procedures, I will pass a page index and maximum rows as inputs. So if I have 10,000 rows in my database table, I will only get 10 rows for my 1st page when I read from the database for more efficient paging. Of course, if you do not need to use ROW_NUMBER, you can just do this:
SELECT
[EmployeeID],
[Name],
[IC],
[DateOfBirth]
FROM
[dbo].[tbEmployee]
ORDER BY
CASE WHEN @OrderByColumn='EmployeeID' THEN [EmployeeID]
WHEN @OrderByColumn='Name' THEN [Name]
WHEN @OrderByColumn='IC' THEN [IC]
WHEN @OrderByColumn='DateOfBirth' THEN [DateOfBirth]
END ASC
Looks easy right. But when you try to sort it, you will not get accurate sorting results. I mentioned earlier, by using CASE statement each of them will have to be the same data type. So my case I need to convert all of them to be string.
However, there are a few considerations to take note:
1) numeric to string
You cannot do this:
CONVERT(varchar(max), [EmployeeID])
CONVERT(varchar(max), [EmployeeID], 2) – try to specify decimal of 2
Well you will not get any error executing this within the stored procedure, but you can never get accurate sorting results
Instead do this:
str([EmployeeID], 28, 2)—this is the only way I can find out how to solve this. Check MSDN for more information
2) datetime to string
You cannot do this
CONVERT(varchar(20), [DateOfBirth])
However, there is a 3rd argument which you need to use
Try this instead
CONVERT(varchar(MAX), [DateofBirth], 102) – I will recommend you to use 102
The final stored procedure will be this
SELECT
ROW_NUMBER()
OVER
(
ORDER BY
CASE WHEN @OrderByColumn='EmployeeID' THEN str([EmployeeID], 28, 2)
WHEN @OrderByColumn='Name' THEN [Name]
WHEN @OrderByColumn='IC' THEN [IC]
WHEN @OrderByColumn='DateOfBirth' THEN CONVERT(varchar(max), [DateOfBirth], 102)
END ASC
) AS Row,
[EmployeeID],
[Name],
[IC],
[DateOfBirth]
FROM
[dbo].[tbEmployee]
Hope you find this useful when you want to do dynamic sorting. Thanks.