Dynamic order by clause in sql server

Introduction

The ORDER BY keyword is used to sort the result set by one or multiple columns and it sorts the records in ascending order by default. To sort the records in a descending order we have to use the DESC keyword.

Order By Columns Dynamically

So here i am going to explain how we can use Order By clause to sort the column values dynamically.

Lets create a sample table and populate the values in that -

DECLARE @employee TABLE
( 
 ID INT IDENTITY(1,1)
 ,Name VARCHAR(50)
 ,Gender VARCHAR(20)
 ,DOB DATETIME
 ,TotProjects INT
)

So here the column which is to be sorted and direction of sorting is passed as a variable. So the above table contains columns “Name” which is VARCHAR, “DOB” which DATETIME and “TotProjects” which is INT. So this is intentional to have different datatypes of columns in my table. 

So now lets use the query to sort the column values dynamically –

DECLARE @searchCol VARCHAR(50) = 'NAME' 
DECLARE @direction VARCHAR(5) = 'ASC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @direction = 'DESC'
      THEN
        CASE 
           WHEN @searchCol = 'NAME' THEN Name 
           WHEN @searchCol = 'GENDER' THEN Gender
           WHEN @searchCol = 'DOB' THEN DOB
           WHEN @searchCol = 'PROJECTS' THEN TotProjects
        END 
  END DESC,
  CASE WHEN @direction = 'ASC'
      THEN
          CASE 
             WHEN @searchCol = 'NAME' THEN Name 
             WHEN @searchCol = 'GENDER' THEN Gender
             WHEN @searchCol = 'DOB' THEN DOB
             WHEN @searchCol = 'PROJECTS' THEN TotProjects
          END
  END ASC

So as in the above figure you can see “CASE” statement is being used in Order By clause. As you observe the columns are sorted based on variable – “@direction“. Column name is also passed from an variable called – “@searchCol“.

So when i tried to execute this with @searchCol setting to “NAME” and @direction setting to “ASC” i will get the below error -

When i googled this, i got multiple links and many of them says – “If you have multiple datatypes in the same CASE statement then it causes this problem“.

So after seeing this i changed my query to have same datatype and it looks like this -

DECLARE @searchCol VARCHAR(50) = 'NAME' 
DECLARE @direction VARCHAR(5) = 'DESC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @direction = 'DESC'
  THEN
  CASE 
   WHEN @searchCol = 'NAME' THEN Name 
   WHEN @searchCol = 'GENDER' THEN Gender
   WHEN @searchCol = 'DOB' THEN CAST(DOB AS VARCHAR(20))
   WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20)) 
  END 
 END DESC,
 CASE WHEN @direction = 'ASC'
  THEN
  CASE 
   WHEN @searchCol = 'NAME' THEN Name 
   WHEN @searchCol = 'GENDER' THEN Gender
   WHEN @searchCol = 'DOB' THEN CAST(DOB AS VARCHAR(20))
   WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20)) 
  END
 END ASC

So in the above screenshot you can see i have used “CAST” to change the datatype of “DOB” and “TotProjects” so now all conditions returns same VARCHAR type. Now i will execute this query with same old paramters  "NAME" and "DESC" - it works.

Now i will execute the same query with @searchCol – “PROJECTS” and @direction - “DESC“. Now i will get results like this -

Oh ho !! it did not work !! Seems like “TotProjects” are not sorting properly.

Now we will go with one more solution to this problem -

DECLARE @searchCol VARCHAR(50) = 'PROJECTS' 
DECLARE @direction VARCHAR(5) = 'DESC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @searchCol = 'NAME' AND @direction = 'ASC' THEN Name END ASC,
 CASE WHEN @searchCol = 'NAME' AND @direction = 'DESC' THEN Name END DESC,
 CASE WHEN @searchCol = 'GENDER' AND @direction = 'ASC' THEN Gender END ASC,
 CASE WHEN @searchCol = 'GENDER' AND @direction = 'DESC' THEN Gender END DESC,
 CASE WHEN @searchCol = 'DOB' AND @direction = 'ASC' THEN DOB END ASC,
 CASE WHEN @searchCol = 'DOB' AND @direction = 'DESC' THEN DOB END DESC,
 CASE WHEN @searchCol = 'PROJECTS' AND @direction = 'ASC' THEN TotProjects END ASC,
 CASE WHEN @searchCol = 'PROJECTS' AND @direction = 'DESC' THEN TotProjects END DESC

and this query works for all datatypes and no where we are changing the datatype. In the above query we are checking both direction and sort column in one condition and it runs good. So performance wise also it works good.

Hope it helps.