Dynamic result ordering in SQL
There are several methods to implement the dynamic ordering of result returned by SQL query. The examples of stored procedures below use the Microsoft's demo database AdventureWorks.
USE AdventureWorks
GO
Sequence of IF..ELSE
The sequence of IF..ELSE looks not so pretty but is efficient and rapid. When you generate SQL code from model or by templates/preprocessor this solution is very useful. Adding the parameter @AscDesc will double the number of IF..ELSE.
CREATE PROCEDURE Person.Contact_List1 @OrderByField nvarchar(255) AS BEGIN IF @OrderByField = 'ContactID' SELECT ContactID, FirstName, LastName FROM Person.Contact ORDER BY ContactID ELSE IF @OrderByField = 'FirstName' SELECT ContactID, FirstName, LastName FROM Person.Contact ORDER BY FirstName ELSE IF @OrderByField = 'LastName' SELECT ContactID, FirstName, LastName FROM Person.Contact ORDER BY LastName ELSE SELECT ContactID, FirstName, LastName FROM Person.Contact END GO
Dynamic SQL
This method is more powerful and short in code. However, be careful: a user should have a rights to read from the table (granted SELECT permissions). It may be unacceptable when you isolate direct data access by stored procedures/function layer. On other hand, there are some extra risks of "SQL injection" attack so you should add some additional code to check it.
CREATE PROCEDURE Person.Contact_List2 @OrderByExpression nvarchar(255) AS BEGIN DECLARE @SQL nvarchar(max) SELECT @SQL = 'SELECT ContactID, FirstName, LastName FROM Person.Contact ORDER BY ' + @OrderByExpression EXEC sp_executesql @SQL END GO
Calculated columns
This method is more standard according to SQL 92 and therefore is portable between different DBMS. You add calculated columns depending on sorting column names and the sort order ASC/DESC
.
Calculated columns should be grouped by their data type or converted in some common type like nvarchar
.
CREATE PROCEDURE Person.Contact_List3 @OrderByField nvarchar(255), @AscDesc int = 0 AS BEGIN SELECT ContactID, FirstName, LastName, /* Group columns by data type */ CASE WHEN @AscDesc = 0 THEN CASE @OrderByField WHEN 'ContactID' THEN ContactID ELSE NULL END ELSE NULL END AS Col1Asc, CASE WHEN @AscDesc = 1 THEN CASE @OrderByField WHEN 'ContactID' THEN ContactID ELSE NULL END ELSE NULL END AS Col1Desc, CASE WHEN @AscDesc = 0 THEN CASE @OrderByField WHEN 'FirstName' THEN FirstName WHEN 'LastName' THEN LastName ELSE NULL END ELSE NULL END AS Col2Asc, CASE WHEN @AscDesc = 1 THEN CASE @OrderByField WHEN 'FirstName' THEN FirstName WHEN 'LastName' THEN LastName ELSE NULL END ELSE NULL END AS Col2Desc FROM Person.Contact ORDER BY Col1Asc ASC, Col1Desc DESC, Col2Asc ASC, Col2Desc DESC END GO
ORDER BY CASE
This method is not ANSI compliant but is easy to understand and works efficiently on SQL Server. You just need to implement several CASE..END expressions inside the ORDER BY and be aware that the portability between DBMS is not guaranteed.
CREATE PROCEDURE Person.Contact_List4 @OrderByField nvarchar(255), @AscDesc int = 0 AS BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact ORDER BY /* Group columns by data type */ -- Integer columns CASE WHEN @AscDesc = 0 THEN CASE @OrderByField WHEN 'ContactID' THEN ContactID ELSE NULL END ELSE NULL END Asc, CASE WHEN @AscDesc = 1 THEN CASE @OrderByField WHEN 'ContactID' THEN ContactID ELSE NULL END ELSE NULL END Desc, -- Text columns CASE WHEN @AscDesc = 0 THEN CASE @OrderByField WHEN 'FirstName' THEN FirstName WHEN 'LastName' THEN LastName ELSE NULL END ELSE NULL END Asc, CASE WHEN @AscDesc = 1 THEN CASE @OrderByField WHEN 'FirstName' THEN FirstName WHEN 'LastName' THEN LastName ELSE NULL END ELSE NULL END Desc END GO