SQL Server Portal

‘Dynamically order by’ clause of any query result set is an old topic and has been discussed on multiple forums. Mostly the solution is being achieved using CASE statement. But what encourages me to write this article is that we can achieve the same with shorter code using  IIF & CHOOSE logical functions shipped with SQL Server 2012.

Method 1 :

In this method we will use the traditional approach and dynamically order by the query result set using CASE statement. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
CASE
WHEN @ORDERBY=1 THEN FirstName
WHEN @ORDERBY=2 THEN LastName
WHEN @ORDERBY=3 THEN JobTitle
END
GO
--OUTPUT

Method 2 :
In this method we will use a NEW approach and dynamically order by the query result set using IIF logical function shipped in SQL Server 2012. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
IIF(@ORDERBY=1,FirstName,IIF(@ORDERBY=2,LastName,JobTitle))
--OUTPUT

Method 3 :
In this method we will use yet another NEW approach and dynamically order by the query result set using CHOOSE logical function shipped in SQL Server 2012. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
CHOOSE(@ORDERBY,FirstName,LastName,JobTitle)
GO
--OUTPUT

Conclusion :
In all of the above methods we used different methods, functions, approaches but the output will remain same and also it will not impact performance. It just reduces the line of code.

Read Full Post »