Journey to SQL Authority with Pinal Dave

One of my friend sent me query asking me how to generate dynamic case statements in SQL. Everytime he tries to run following query he is getting Error 156 : Incorrect syntax near the keyword. He was frustrated with following two queries. There are two different ways to solve the problem when user want to

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

ResultSet:
Msg 156, Level 15, State 1, Line 8
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

ResultSet:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword ‘DESC’.

Correct Query 1 : Using CASE to OrderBy

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

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

You can use any of above mentioned correct method and use CASE statement to ORDER BY dynamically. If you are interested to learn how to ORDER BY dynamically more than one column you can read my previous article SQL SERVER - CASE Statement in ORDER BY Clause - ORDER BY using Variable.

Reference : Pinal Dave (http://www.SQLAuthority.com)