Changing the direction of results from a SQL Server ORDER BY clause
I have a stored procedure which returns me a list of items, and I want the directional order of them to change based on a parameter. I didn't want to resort to dynamic SQL, so I had a bit of a hunt around and found the excellent blog post Dynamic/Conditional Order By Clause in SQL Server/T-SQL. Here's my result:
CREATE Procedure [dbo].[mp_GetItems]
(
@OldestFirst BIT
)
AS
SELECT
Item.ID, Item.Description
FROM
Items
ORDER BY
CASE WHEN @OldestFirst = 1 THEN CreatedDate END ASC,
CASE WHEN @OldestFirst = 0 THEN CreatedDate END DESC
GO
which works a treat. The key is, as mentioned in the blog post
Ascending and Descending actions need to be grouped into separate CASE statements, separated with a comma
