Passing dynamic order by in stored procedure

Question

I am creating below stored procedure.

declare @PageNum as Int
declare @PerPageResult as Int
declare @StartDate as varchar(25)
declare @EndDate as varchar(25)
declare @SortType as Varchar(50)
declare @SortDirection as Varchar(4)
set @PageNum=1
set @PerPageResult=20
set @StartDate='2008-02-08'
set @EndDate='2015-02-08'
set @SortType='RegDate'
set @SortDirection='Desc'
declare @Temp Table(RowNum int, RegDate Date, Registered int, Female int, Male int, [Join] int, Rebill int, TotalPointsEarned int, Expire int)
declare @sort varchar(50)
Insert into @Temp
    Select ROW_NUMBER() over (order by @SortType+' '+@SortDirection) As RowNum, * From (    
    SELECT 
    CAST(m.registrationdate AS Date) as RegDate,    
    count(m.id) Registered,
    count(CASE WHEN m.gender='F' then 'F' end) As Female,
    count(CASE WHEN m.gender='M' then 'M' end) As Male
    count(CASE WHEN p.paymenttransactiontype='N' then 'N' end) As [Join],
    count(CASE WHEN p.paymenttransactiontype='R' then 'R' end) As Rebill,
    count(m.tokensearned) As TotalPointsEarned,
    count(CASE WHEN p.paymenttransactiontype='E' then 'E' end) As Expire
    from member m
    join payment p on m.id=p.id_member
    join user_role u on u.member_id=m.id
    where u.role_id <> 3
    and CAST(m.registrationdate AS Date) > @StartDate and CAST(m.registrationdate AS Date) < @EndDate
    GROUP BY CAST(m.registrationdate AS Date)
    ) as aa 
    Select * from @Temp Where RowNum>((@PageNum-1)*@PerPageResult) and RowNum<=@PerPageResult * @PageNum
    Order by @SortType+' '+@SortDirection

In above when i pass the Order by clause dynamically, its not sorting the data properly but when i write column name explicitly, it works fine. Might be its taking @SortType+' '+@SortDirection as varchar rather than Date

I tried writing Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc, but it didn't work

How can i pass order by dynamically here.

Edit: @Andomar: I tried your provided solution and added one more field for Date type. And it didn't work too.

below is what i did.

create table t1 (id int, name varchar(50), dt date);
insert t1 values 
    (1, 'Chihiro Ogino','2009-02-08'), 
    (2, 'Spirit of the Kohaku River','2008-02-08'), 
    (3, 'Yubaba','2012-02-08');



declare @sortColumn varchar(50) = 'dt'
declare @sortOrder varchar(50) = 'ASC'

select  *
from    t1
order by
        case
        when @sortOrder <> 'ASC' then 0
        when @sortColumn = 'id' then id
        end ASC
,       case
        when @sortOrder <> 'ASC' then ''
        when @sortColumn = 'name' then name
        end ASC
,       case
        when @sortOrder <> 'ASC' then ''
        when @sortColumn = 'dt' then name
        end ASC
,       case
        when @sortOrder <> 'DESC' then 0
        when @sortColumn = 'id' then id
        end DESC
,       case
        when @sortOrder <> 'DESC' then ''
        when @sortColumn = 'name' then name
        end DESC
,       case
        when @sortOrder <> 'DESC' then ''
        when @sortColumn = 'dt' then name
        end DESC

Answer 1

Since SQL Server 2016 you could use a JSON string.

Just using a simple table to illustrate the concept in this example.

CREATE TABLE t1 (c1 INT, c2 INT, c3 INT)
INSERT INTO t1 VALUES (1,2,3),(3,1,2),(2,3,1),(1,3,2),(2,1,3),(3,2,1);

DECLARE
@order        NVARCHAR(MAX) = '{"c1":"DESC","c3":"ASC"}'

SELECT *
  FROM t1
  ORDER BY
    CASE JSON_VALUE(@order, '$.c1') WHEN 'DESC' THEN c1 END DESC,
    CASE JSON_VALUE(@order, '$.c1') WHEN 'ASC' THEN c1 END ASC,
    CASE JSON_VALUE(@order, '$.c2') WHEN 'DESC' THEN c2 END DESC,
    CASE JSON_VALUE(@order, '$.c2') WHEN 'ASC' THEN c2 END ASC,
    CASE JSON_VALUE(@order, '$.c3') WHEN 'DESC' THEN c3 END DESC,
    CASE JSON_VALUE(@order, '$.c3') WHEN 'ASC' THEN c3 END ASC;

If a column is not listed in the JSON string, or its value is invalid, then it will be ignored.

A blank JSON string (i.e. {}) does work. There is just no specific order.

If the column sort order also needs to be dynamic this can be expanded on by adding a sequence number variable within the JSON string. The ORDER BY code will also have to be repeated for each iteration of the sequence.

DECLARE
@order        NVARCHAR(MAX) = '{"1":{"c1":"DESC"},"2":{"c3":"ASC"}}'

SELECT *
  FROM t1
  ORDER BY
    CASE JSON_VALUE(@order, '$."1".c1') WHEN 'DESC' THEN c1 END DESC,
    CASE JSON_VALUE(@order, '$."1".c1') WHEN 'ASC' THEN c1 END ASC,
    CASE JSON_VALUE(@order, '$."1".c2') WHEN 'DESC' THEN c2 END DESC,
    CASE JSON_VALUE(@order, '$."1".c2') WHEN 'ASC' THEN c2 END ASC,
    CASE JSON_VALUE(@order, '$."1".c3') WHEN 'DESC' THEN c3 END DESC,
    CASE JSON_VALUE(@order, '$."1".c3') WHEN 'ASC' THEN c3 END ASC,

    CASE JSON_VALUE(@order, '$."2".c1') WHEN 'DESC' THEN c1 END DESC,
    CASE JSON_VALUE(@order, '$."2".c1') WHEN 'ASC' THEN c1 END ASC,
    CASE JSON_VALUE(@order, '$."2".c2') WHEN 'DESC' THEN c2 END DESC,
    CASE JSON_VALUE(@order, '$."2".c2') WHEN 'ASC' THEN c2 END ASC,
    CASE JSON_VALUE(@order, '$."2".c3') WHEN 'DESC' THEN c3 END DESC,
    CASE JSON_VALUE(@order, '$."2".c3') WHEN 'ASC' THEN c3 END ASC,

    CASE JSON_VALUE(@order, '$."3".c1') WHEN 'DESC' THEN c1 END DESC,
    CASE JSON_VALUE(@order, '$."3".c1') WHEN 'ASC' THEN c1 END ASC,
    CASE JSON_VALUE(@order, '$."3".c2') WHEN 'DESC' THEN c2 END DESC,
    CASE JSON_VALUE(@order, '$."3".c2') WHEN 'ASC' THEN c2 END ASC,
    CASE JSON_VALUE(@order, '$."3".c3') WHEN 'DESC' THEN c3 END DESC,
    CASE JSON_VALUE(@order, '$."3".c3') WHEN 'ASC' THEN c3 END ASC;

Note: The quotes around the sequence number in the JSON_VALUE statement is required here because the variable name starts with a number.

This method does assume that there are no repeating sequence or column name entries within the JSON string.

You may be able to adapt this to use XML if you are on an earlier version of SQL Server (2005 - 2014). I have not tried it.

Answer 2

There are two basic approaches to building dynamically orderable stored procedures:

1. Pass in the ORDER BY clause as a parameter to the stored procedure. In the stored procedure, build up the SQL statement in a string and then execute this statement using EXEC or sp_ExecuteSql.

   -- This Method is used when your Column names are dynamic 
   -- We need to create a dynamic query and Execute it as shown below.
   
   CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS
   
   -- Create a variable @SQLStatement
   DECLARE @SQLStatement varchar(255)
   
   -- Enter the dynamic SQL statement into the
   -- variable @SQLStatement
   SELECT @SQLStatement =  'SELECT EmployeeID, FirstName, LastName, SSN, Salary  
    FROM Employees ORDER BY '+ @OrderByClause+''
   
   -- Execute the SQL statement
   EXEC(@SQLStatement)
   

2. Pass in the column to sort by and then use a CASE statement in the ORDER BY clause to order the results according to the input parameter value.

   --This method is used when you column name is not dynamic 
   
   SELECT EmployeeID, FirstName, LastName, SSN, Salary
   FROM Employees
   ORDER BY
     CASE WHEN @ColumnName='LastName' THEN LastName
          WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)
          WHEN @ColumnName='SSN' THEN SSN
     END
   

Answer 3

@Andomar's answer help solve a similar issue. I needed to sort on any number of 23 different columns, in any order. I ended up with the following:

create table sorting(ID int, columnName varchar(50), sort varchar(10), position int)
insert into sorting 
values(1,'column1','DESC',1),
      (1,'column2','ASC',2),
      ...              
      (1,'columnN','DESC',N)

Adding parameter @sort to the SP to identify the entries in sorting:

ORDER BY ISNULL(STUFF((SELECT ', ' + a.columnName + ' ' +  a.sort 
                    FROM sorting a
                    WHERE a.ID = @sort
                    ORDER BY a.position ASC
            FOR XML PATH('')), 1, 2, ''),NULL)

Answer 4

You can use a complicated order by clause. That requires one case for each sort direction and each data type. With this example dataset:

create table t1 (id int, name varchar(50), created date);
insert t1 values 
    (1, 'Chihiro Ogino', '2012-01-01'), 
    (2, 'Spirit of the Kohaku River', '2012-01-03'), 
    (3, 'Yubaba', '2012-01-02');

You could use an order by clause like:

declare @sortColumn varchar(50) = 'created'
declare @sortOrder varchar(50) = 'DESC'

select  *
from    t1
order by
        case
        when @sortOrder <> 'ASC' then 0
        when @sortColumn = 'id' then id
        end ASC
,       case
        when @sortOrder <> 'ASC' then ''
        when @sortColumn = 'name' then name
        end ASC
,       case
        when @sortOrder <> 'ASC' then cast(null as date)
        when @sortColumn = 'created' then created
        end ASC
,       case
        when @sortOrder <> 'DESC' then 0
        when @sortColumn = 'id' then id
        end DESC
,       case
        when @sortOrder <> 'DESC' then ''
        when @sortColumn = 'name' then name
        end DESC
,       case
        when @sortOrder <> 'DESC' then cast(null as date)
        when @sortColumn = 'created' then created
        end DESC

Working example at SQL Fiddle.

Another option is to create the query dynamically, and run it with exec. For example:

declare @sql nvarchar(max)
set @sql = 'select * from YourTable order by ' + @sortColumn + ' ' + @sortDir
exec (@sql)

Answer 5

something like this should work :

ORDER BY
  CASE WHEN @SortDirection = 'ASC'  THEN @SortType END ASC,
  CASE WHEN @SortDirection = 'DESC' THEN @SortType END DESC