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

Wheelhouse

gender='SH': haven't seen that before... – Alysiaalyson

@MitchWheat: Ignore it :P – Wheelhouse

search for "ORDER BY CASE" – Alysiaalyson

instead of case, do we have better solution as i have 11 columns for sort type. – Wheelhouse

Or, sort on the client! It's so much easier! – Macroscopic

@archangel76: Sorting on client side won't help, suppose you have 10000 records in database and you're showing 10 at a time. Clientside sort would occur in those 10 records only which is even not correct. – Wheelhouse

Very true. I found this question looking for just the sort, not the pagination, and realized that sorting in the .NET code was actually what I needed, and that was sufficient. The question didn't help me, and so I was posting to help those that only needed the sort and had run across this. Sorry I was previously unclear. – Macroscopic

Answer 1

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)

Thermal

instead of case, do we have better solution as i have 11 columns for sort type. – Wheelhouse

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

@JitendraPancholi: That should work, if the variables are set correctly. – Thermal

you can check in my query above. Its not sorting properly. – Wheelhouse

Can you please add 1 more column of date type and add sorting for that also and check. It won't work. – Wheelhouse

New example with multiple columns and multiple data types (a separate case is required for each data type and each sort order. Dynamic SQL is a bit simpler I'd think.) – Thermal

Let me check, and dynamic sql would be a performance issue..don't you think. – Wheelhouse

Quite the opposite, dynamic SQL is usually best for performance. It makes the order by clause much simpler. For a date column, make sure the type is correct. Try ... when @sortOrder <> 'ASC' then cast(null as datetime) ... – Thermal

Yeah, got it. its working now. thanx. And one more thing, dynamic sql never compiles and its not good for performance. – Wheelhouse

@JitendraPancholi: Dynamic SQL is compiled and its query plan is reused whenever you run the exact same query. This is actually an advantage: compiling a specific plan for each query is often better than running a generic plan that handles all cases. – Thermal

let us continue this discussion in chat – Thermal

It should be noted that dynamic SQL is an injection vulnerability. Don't use dynamic SQL if the parameter is controlled by the client. Even if it's just a multiple choice, if the string is what is sent from the client, they can use a custom POST to change the string that is sent up. – Rowel

This article explains all the options and I learned a LOT (things I didn't know to ask) from this article: The Curse and Blessings of Dynamic SQL by MVP Erland Sommarskog. sommarskog.se/dynamic_sql.html – Driftwood

I used this to simplify a large Entity Framework query by doing the filtering, sorting and OFFSET/FETCH NEXT-based pagination in an SQL function, then loading the objects with EF based on the returned primary keys. One thing to note with that approach: Include a ROW_NUMBER() over the same ORDER BY clause in your return values and use it to order the objects. – Lanthanum

Is it not recomanded to turn the sp into a table function ? Table functions act as ordinery tables – Logway

Answer 2

@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)

Villus

Good idea for sorting with multiple columns – Curst

Answer 3

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
   

Necessitous

You should at least site this is from 4guysfromrolla.com/webtech/010704-1.shtml – Evincive

Incomplete, Haven't Explained Dynamic Sorting Direction. – Bohs

The Salary column is a number, the CONVERT will make it left-aligned. you might need to do RIGHT(SPACE(20) + FORMAT(Salary, '#.00'), 20) – Canebrake

Answer 4

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.

Epiblast

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

Unblock

Ordering by SortType won't work. SQL Server doesn't allow a variable in the place of a column name. – Thermal

instead of case, do we have better solution as i have 11 columns for sort type. – Wheelhouse

@Thermal No, Sql server 2008 r2 accepts it, I just tried ( just forget to put @ at the first of the names at first) – Unblock

@JitendraPancholi you want them to be different in SortDirection with each other? – Unblock

@MaryamArshi: It will accept it, but it won't work as you expect. It'll sort on the constant value of @SortType, i.e., it won't sort at all – Thermal

@MaryamArshi: yes, it won't work properly. this is what i mentioned above in my question too. – Wheelhouse

It might not work for a variable field, but it works perfectly well for a constant field if anyone wants to do that as I did, but with a variable sort direction: ORDER BY CASE WHEN @SortDesc=1 THEN DateField END DESC, CASE WHEN @SortDesc=0 THEN DateField END ASC – Alterable