Dynamic Sorting In SQL Server TSQL Stored Procedures

Published: {ts '2013-04-09 00:00:00'}
Author: Steven Neiland
Site Url: http://www.neiland.net/article/dynamic-sorting-in-sql-server-tsql-stored-procedures/

In a previous posting I discussed Dynamic Paging In TSQL Stored Procedures. As a followup today I am going to show a slightly simpler usage of the same technique to allow for dynamic sorting inside a SQL server stored procedure.

Start: Basic Query

To start lets take a simple query to get a userId and username from a table of users.


SELECT 
	userId
	,username 
FROM 
	users

For fun lets throw in a @top variable to limit the number of records we get.


DECLARE @top INTEGER

SET @top = 5

SELECT 
	TOP(@top)
	userId
	,username 
FROM 
	users

Give that a run and everything works fine. We get back just 5 records.

Trying To Sort With Variables (Won't work)

Nothing difficult about that. Now lets try to sort the returned records by userId using variables.


DECLARE @SortCol VARCHAR(100)
DECLARE @SortDir VARCHAR(4)
DECLARE @top INTEGER

SET @SortCol = 'userId'
SET @SortDir = 'ASC'
SET @top = 5

SELECT 
	TOP(@top) 
	userId
	,username 
FROM 
	users
ORDER BY
	@SortCol @SortDir

Wouldn't it be nice if it was this simple. Well sorry but because of how SQL Server works this is not possible. Instead we get a nice error:

Incorrect syntax near '@SortDir'.

Solution Use A Case Statement

As with the dynamic paging problem, the solution is to use a series of CASE statements like this.


DECLARE @SortCol VARCHAR(100)
DECLARE @SortDir VARCHAR(4)
DECLARE @top INTEGER

SET @SortCol = 'userId'
SET @SortDir = 'ASC'
SET @top = 5

SELECT 
	TOP(@top) 
	userId
	,username 
FROM 
	users
	
ORDER BY
	CASE WHEN @SortCol = 'userId' AND @SortDir = 'ASC' 
		THEN userId END ASC, 
	CASE WHEN @SortCol = 'userId' AND @SortDir = 'DESC' 
		THEN userId END DESC,
	CASE WHEN @SortCol = 'username' AND @SortDir = 'ASC' 
		THEN username END ASC, 
	CASE WHEN @SortCol = 'username' AND @SortDir = 'DESC' 
		THEN username END DESC

Give this a run and you should now be able to sort in either direction by userId or username.

Yes it's very wordy, and gets more so with each column you want to be able to sort on. However to date it is the only solution I have found to work other than dynamically creating sql inside the stored procedure. And we all know that it is a very bad idea to create dynamic sql don't we. So there you go.