Add new comment

Dynamic result ordering in SQL

Submitted by st on

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