Passing Dynamic Order by in Stored Procedure

Passing dynamic order by in stored procedure

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)

Dynamic order direction

You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value:

ORDER BY
      CASE WHEN @OrderDirection = 0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection = 1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy           
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END DESC

Dynamic Order By clause with column and sort information coming from a table?

This will only work if you are using dynamic SQL. I was not able to get it to work as-is.

Pass ANY string as a in parameter to a stored procedure in mysql (dynamic)

OK, got it. Since I pass a string in TesterName I do need of course mark it between 'xxx' in this case.
So it works by

set @t3=CONCAT("update localdevid.",TableName," set TesterID='",TesterName,"' where SensorID=",DevID);

Same applies for the timestamp:

set @mydate=DATE_FORMAT(now(),"%d.%m.%y %h:%i:%S");
set @t4=CONCAT("update localdevid.",TableName," set tAss='",@mydate,"' where SensorID=",DevID);

---

Related Topics