T-sql Conditional Order By

sql

sql-injection

dynamic-sql

conditional-ordering

byNikita Barsukov·Oct 4, 2024

For dynamically changing sorting criteria, use a CASE statement in the ORDER BY. For instance, sorting a People table by LastName or BirthDate based on 'retired' status is as follows:

SELECT FirstName, LastName, BirthDate, IsRetired
FROM People
ORDER BY
  CASE WHEN IsRetired = 1 THEN BirthDate ELSE LastName END ASC;

Here, retired individuals (IsRetired = 1) will be sorted by BirthDate, while non-retired individuals by LastName. This approach specifically tailors the sort order according to row-specific data.

Deploying dynamic SQL for conditional ordering

In scenarios where conditional ordering gets complicated, dynamic SQL might be your go-to. This tactic conditionally crafts the ORDER BY clause based on requisite needs:

DECLARE @sql NVARCHAR(MAX), @orderBy NVARCHAR(100) = N'LastName';

SET @sql = N'SELECT FirstName, LastName, BirthDate FROM People ORDER BY ' + @orderBy;

-- Caution: Dragons ahead! 🐉SQL Injection might haunt you.
EXEC sp_executesql @sql;

Dynamic SQL lets you modify the sorting column runtime, adding an element of flexibility. Be cautious, and always sanitize and validate user inputs to prevent SQL injection attacks.

Making the choice: CASE over dynamic SQL?

Simplicity: Stick to CASE for column-specific, straightforward conditions.
Performance: Complex or multi-column sorting? Dynamic SQL often crafts better query plans.
Safety: Validated inputs against SQL injection are mandatory for any dynamic SQL.

Knowing your CASE - Compatibility and restrictions

Data Types: CASE outputs must be friendly with compatible data types.
Collation: Stay alert of implicit collation conversions causing errors or unintended performance pitfalls.
Sort Direction: Mixing ASC and DESC within a single CASE statement isn't supported natively.

-- Sadly, attempts to party with 'ASC' and 'DESC' together results in party poopers 😒
ORDER BY CASE WHEN @condition = 'ASC' THEN LastName END ASC,
         CASE WHEN @condition = 'DESC' THEN LastName END DESC;

Conditional Order By Approaches

Conditional sorting with multiple CASE statements

For dealing with multiple columns, multiple CASE statements can weave magic albeit with added complexity:

ORDER BY
  CASE WHEN @SortBy = 'Name' THEN LastName END, -- Even names have their "case" to show off! 😏
  CASE WHEN @SortBy = 'Date' THEN BirthDate END;

The might of dynamic SQL

For multidimensional sorting where columns and directions aren't constants, dynamic SQL is the superhero. Though with great power comes great responsibility – Always validate inputs or use parameterized queries to avoid security loopholes.

Tackling CASE hurdles

Predefine types: Avoid casting overheads; Ensure CASE operates on matching types.
Handy with NULLs: Remember, CASE treats NULL as the lowest possible value when sorting.