Dynamic Sorting in MS SQL Server
Let users sort their columns in MS SQL.
Join the DZone community and get the full member experience.
Join For FreeWhat Is Sorting?
Sorting is a process of arranging items in a sequence by some standard or principle.
In MS SQL, the ORDER BY clause is used to arrange the data in ascending or descending order.
How Dynamic Sorting Works in MS SQL
Let's think of a web application that displays employee information in a grid with FirstName, LastName, Email, Title, Phone, and few other columns. On clicking the column heading the result set should sort accordingly.
Here, the sort column is dynamic, it depends on the user who clicks. To implement the sort on all the columns we use the CASE statement in the ORDER BY clause.
In the query below, @SortColumn defines the column that needs to be sorted, and @SortType defines ascending or descending. And the columns required for dynamic sorting are included in the CASEs under ORDER BY.
DECLARE @SortColumn AS VARCHAR(100) = 'firstname'
,@SortType AS VARCHAR(100) = 'asc'
SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY
CASE WHEN @SortColumn = 'FirstName' AND @SortType ='ASC' THEN FirstName END ASC,
CASE WHEN @SortColumn = 'FirstName' AND @SortType ='DESC' THEN FirstName END DESC,
CASE WHEN @SortColumn = 'LastName' AND @SortType ='ASC' THEN LastName END ASC,
CASE WHEN @SortColumn = 'LastName' AND @SortType ='DESC' THEN LastName END DESC,
CASE WHEN @SortColumn = 'Title' AND @SortType ='ASC' THEN Title END ASC,
CASE WHEN @SortColumn = 'Title' AND @SortType ='DESC' THEN Title END DESC,
CASE WHEN @SortColumn = 'EmailAddress' AND @SortType ='ASC' THEN EmailAddress END ASC,
CASE WHEN @SortColumn = 'EmailAddress' AND @SortType ='DESC' THEN EmailAddress END DESC
In a real-time application, this logic can be written in the stored procedure which is called on clicking on the column which needs to be sorted, the respective column name, and the sort order to be passed as parameters to the stored procedure.
Opinions expressed by DZone contributors are their own.
Comments