Dynamic Sorting in SQL Server

Dynamic Sorting in SQL Server

Dynamic sorting in SQL Server allows you to sort the result set of a query based on one or more columns in a dynamic and flexible way. This means that you can determine the sorting order of the result set at runtime, rather than hardcoding it in the query.

There are several ways to implement dynamic sorting in SQL Server, but one common approach is to use the ORDER BY clause with a variable that contains the column name(s) to sort on.

Here’s an example

DECLARE @SortColumn NVARCHAR(50) = 'LastName'
DECLARE @SortOrder NVARCHAR(4) = 'ASC'

SELECT *
FROM Customers
ORDER BY 
  CASE WHEN @SortOrder = 'ASC' THEN
    CASE @SortColumn
      WHEN 'FirstName' THEN FirstName
      WHEN 'LastName' THEN LastName
      WHEN 'City' THEN City
    END
  END ASC,
  CASE WHEN @SortOrder = 'DESC' THEN
    CASE @SortColumn
      WHEN 'FirstName' THEN FirstName
      WHEN 'LastName' THEN LastName
      WHEN 'City' THEN City
    END
  END DESC

In this example, the @SortColumn variable determines which column to sort on, and the @SortOrder variable determines whether to sort in ascending or descending order. The CASE statements in the ORDER BY clause dynamically generate the sort expression based on the values of these variables.

Note that dynamic sorting can be potentially risky, as it can expose your database to SQL injection attacks. It’s important to validate and sanitize any user input that is used to determine the sorting order.