Have you ever encountered a situation where you needed to generate dynamic case statements in SQL Server? If so, you’re not alone. Many developers face this challenge when trying to order their query results based on user-defined criteria. In this article, we will explore two different approaches to solving this problem.

The Problem

Let’s say you have a table called “WorkOrder” in the AdventureWorks database, and you want to retrieve records based on a specific product ID. Additionally, you want to order the results either in ascending or descending order, depending on a variable called “@OrderDirection”.

Your initial attempts at solving this problem might look something like this:

Incorrect Query 1:

USE AdventureWorks
GO

DECLARE @OrderDirection VARCHAR(5)
SET @OrderDirection = 'DESC'

SELECT *
FROM Production.WorkOrder
WHERE ProductID = 722
ORDER BY OrderQty CASE WHEN @OrderDirection = 'DESC' THEN DESC ELSE ASC END
GO

Result: Error 156 – Incorrect syntax near the keyword ‘CASE’.

Incorrect Query 2:

USE AdventureWorks
GO

DECLARE @OrderDirection VARCHAR(5)
SET @OrderDirection = 'DESC'

SELECT *
FROM Production.WorkOrder
WHERE ProductID = 722
ORDER BY CASE WHEN @OrderDirection = 'DESC' THEN OrderQty DESC ELSE OrderQty ASC END
GO

Result: Error 156 – Incorrect syntax near the keyword ‘DESC’.

The Solution

Fortunately, there are two correct ways to generate dynamic case statements in SQL Server.

Correct Query 1: Using CASE to ORDER BY

USE AdventureWorks
GO

DECLARE @OrderDirection VARCHAR(5)
SET @OrderDirection = 'DESC'

SELECT *
FROM Production.WorkOrder
WHERE ProductID = 722
ORDER BY CASE WHEN @OrderDirection = 'DESC' THEN OrderQty END DESC,
         CASE WHEN @OrderDirection = 'ASC' THEN OrderQty END
GO

This query uses the CASE statement within the ORDER BY clause to dynamically order the results. It checks the value of the “@OrderDirection” variable and orders the records accordingly. If the value is ‘DESC’, it orders by “OrderQty” in descending order. If the value is ‘ASC’, it orders by “OrderQty” in ascending order.

Correct Query 2: Using CASE to Multiply with Negative Number

USE AdventureWorks
GO

DECLARE @OrderDirection VARCHAR(5)
SET @OrderDirection = 'DESC'

SELECT *
FROM Production.WorkOrder
WHERE ProductID = 722
ORDER BY OrderQty * CASE WHEN @OrderDirection = 'DESC' THEN -1 ELSE 1 END
GO

This query takes a slightly different approach. Instead of using the CASE statement within the ORDER BY clause, it multiplies the “OrderQty” column by a negative number (-1) when the “@OrderDirection” variable is ‘DESC’. This effectively reverses the order of the results.

Conclusion

By using the correct methods mentioned above, you can easily generate dynamic case statements in SQL Server and order your query results based on user-defined criteria. Whether you choose to use the CASE statement within the ORDER BY clause or multiply with a negative number, these techniques will help you achieve the desired outcome.

If you’re interested in learning more about using the CASE statement in the ORDER BY clause, you can check out my previous article on “SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable”.