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

There are various approaches to implementing dynamic sorting in SQL Server, but the most frequent is to utilize the ORDER BY clause with a variable containing 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.

HostForLIFEASP.NET SQL Server 2022 Hosting