Dynamic Sorting in Stored Procedure

One of the reasons we tend to build SQL statements in our application code is because it is difficult to sort the results using different fields. You have at least a couple solutions to this scenario, if you wish to use stored procedures.

1. Do not sort the data in the stored procedure; sort the results in the application
2. Modify your procedure to use different sorting by having different paths in the procedure, or use a sorting function that can be defined programmatically.

Using different program paths with different sorting order is pretty straight forward. So, today I am going to demonstrate sorting using different fields or sorting directions using the AdbentureWorks2012 database. I’m selecting from the HumanResources.Employee table allowing sort on either the MaritalStatus or Gender columns. The results may be sorted in Ascending or Descending order. If no sort is provided, it returns sorted by the BuinsessEntityId in Ascending order.

DECLARE @SortField VARCHAR(40) = 'Gender'

DECLARE @SortDirection varchar(4) = 'ASC'

SELECT *

,CASE

WHEN @sortfield='MaritalStatus' and @sortdirection='ASC'

THEN (ROW_NUMBER() OVER(ORDER BY MaritalStatus ASC))

WHEN @sortfield='MaritalStatus' and @sortdirection='desc'

THEN (ROW_NUMBER() OVER(ORDER BY MaritalStatus DESC))

WHEN @sortfield='Gender' and @sortdirection='asc'

THEN (ROW_NUMBER() OVER(ORDER BY Gender ASC))

WHEN @sortfield='Gender' and @sortdirection='desc'

THEN (ROW_NUMBER() OVER(ORDER BY Gender DESC))

ELSE ROW_NUMBER() OVER(ORDER BY BusinessEntityId ASC)

END as RowNumber

from HumanResources.employee

ORDER BY RowNumber, BusinessEntityId

If you are building the SQL in your application, it’s probably more effective to build your ORDER BY clause programmatically. However, when you need to have more flexible sorting encapsulated in a stored procedure, this option has proven effective.

Cheers,

Ben