T-SQL Conditional Order By

by

CASE is an expression that returns a value. It is not for control-of-flow, like IF. And you can’t use IF within a query.

Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression must return the same type, or be implicitly convertible to the same type. I wouldn’t try that with strings and dates. You also can’t use CASE to specify sort direction.

SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY 
  CASE WHEN @sortDir="asc" AND @sortOrder="name" THEN name END,
  CASE WHEN @sortDir="asc" AND @sortOrder="created_date" THEN created_date END,
  CASE WHEN @sortDir="desc" AND @sortOrder="name" THEN name END DESC,
  CASE WHEN @sortDir="desc" AND @sortOrder="created_date" THEN created_date END DESC;

An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:

IF @sortDir NOT IN ('asc', 'desc')
  OR @sortOrder NOT IN ('name', 'created_date')
BEGIN
  RAISERROR('Invalid params', 11, 1);
  RETURN;
END

DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
  FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;

EXEC sp_executesql @sql;

Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran:

http://sqlperformance.com/conditional-order-by

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. How do I escape a single quote in SQL Server?
  6. Simple way to transpose columns and rows in SQL?
  7. Only inserting a row if it’s not already there
  8. Unpivot with column name
  9. Selecting data from two different servers in SQL Server
  10. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  11. SQL JOIN vs IN performance?
  12. Most efficient way in SQL Server to get date from date+time?
  13. Convert varchar into datetime in SQL Server
  14. TSQL Pivot without aggregate function
  15. How to generate a range of dates in SQL Server
  16. SQL RANK() versus ROW_NUMBER()
  17. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  18. How do I delete from multiple tables using INNER JOIN in SQL server
  19. Check if a row exists, otherwise insert
  20. SQL : BETWEEN vs =
  21. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  22. How do I perform a GROUP BY on an aliased column in SQL Server?
  23. How do I make a composite key with SQL Server Management Studio?
  24. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  25. TransactSQL to run another TransactSQL script
  26. Getting Dates between a range of dates
  27. The most elegant way to generate permutations in SQL server
  28. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  29. How to get Previous Value for Null Values
  30. CONCAT_WS() for SQL Server

Leave a Comment