Let me show you, how CASE expression can be used in an ORDER BY clause to determine the sort order of the rows based on a given column value.

Example 1 :

Let us assume that we need to sort [CityName] based on @ORDER variable when we set to 1, the result set should sort by [CityName] in ascending order and @ORDER set to 2 should be in descending order.

Normal result without order by clause would be :

SELECT [CityID],[CityName] FROM [DBO].[City]

I have modified the above statement slightly to make ORDER BY based on the condition which is shown below:

DECLARE @ORDER TINYINT
SET @ORDER = 1
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC,
CASE WHEN @ORDER = 2 THEN [CityName] END DESC

Let us execute the above statement and see the output.

From the above result set, we could identify that the records were sorted by [CityName] in ascending order as @ORDER value is 1.

Now, let us change @ORDER value to 2 (descending order) and see the result.

DECLARE @ORDER TINYINT
SET @ORDER = 2
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC
CASE WHEN @ORDER = 2 THEN [CityName] END DESC
GO

As expected, the result set has been sorted by [CityName] in descending order.

Example 2 :

The same CASE expression can also be used to sort columns based on conditions. See the below example.

DECLARE @ColumnOrder VARCHAR
SET @ColumnOrder = ‘CityName’

SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = ‘CityName’ THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = ‘CityID’ THEN [CityID]END ASC
GO

The same statement can be used in Stored Procedure as given below :

CREATE PROCEDURE [DBO].[STP_City]
(
@ColumnOrder VARCHAR(10)
)
AS
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = ‘CityName’ THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = ‘CityID’ THEN [CityID]END ASC
GO

EXECUTE [DBO].[STP_City] ‘CityName’
GO

Note : For this example, I have used only variables, even column names also be used in place of variables.