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.
Dynamic sorting by column depending on value in column
To answer. I did finally find the solution. It was quite simple and whilst I read through other answers, I was confused as to why it did not work for me. Apparently the index of a column will not work when using case when.
In the end, I put up the whole query with unions into a subquery with the solution being:
SELECT * FROM(
SELECT id, name, sortbycolumn FROM table
WHERE id = :in_id
UNION
SELECT id, name, null sortbycolumn FROM table
WHERE id = :in_id
)
ORDER BY
case when sortbycolumn = 1 THEN id,
case when sortbycolumn = 2 then name
else id end
How to specify dynamic order by clause in Oracle
Order by two outer CASE
expressions, one ASC
one DESC
. In the first one check if you want to sort ASC
and if not let the expression return a constant or NULL
, i.e. it doesn't change the order. For the case you want to sort ASC
add an inner CASE
expression, that returns the column to sort by. Analogously handle the DESC
case.
...
ORDER BY CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 1 THEN
city_code
WHEN in_order_by = 2 THEN
city_name
...
END
END ASC,
CASE
WHEN in_order_dir = 'DESC' THEN
CASE
WHEN in_order_by = 1 THEN
city_code
WHEN in_order_by = 2 THEN
city_name
...
END
END DESC;
Maybe you have to adapt it because of type incompatibilities between the columns, I can't tell that from what you posted. But it should convey the general concept.
Edit:
On the type problem:
One possibility is to convert the columns to compatible data types in a way the order is kept. For instance, if you have a char_column
(of the type char
), a date_column
and an integer_column
(of date
and integer
) you could do
to_char(date_column, 'YYYYMMDDHH24MISS')
to convert the date and
lpad(11, 38, '0')
to convert the integer to char
.
The cleaner thing to do, but the one with more work to do (for the programmer, in terms of performance there should be no significant difference whatsoever) is to split the expressions once more. I.e. have an outer CASE
for each type your columns are for each direction.
Like
ORDER BY CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 1 THEN
char_column1
WHEN in_order_by = 2 THEN
char_column2
...
END
END ASC,
CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 3 THEN
date_column1
WHEN in_order_by = 4 THEN
date_column2
...
END
END ASC,
and so on for all types and analogously for DESC
. Remember, if the column isn't meant to be sorted after such CASE
will yield NULL
and sorting by that won't influence the order.
Related Topics
How to Get Time Part from SQL Server 2005 Datetime in 'Hh:Mm Tt' Format
SQL Query to Count() Multiple Tables
Oracle Insert Failure:Not a Valid Month
No Fields for Dynamic SQL Stored Procedure in Ssrs with Set Fmtonly
How Can a Do a "Greatest-N-Per-Group" Query in Django
ASP.NET MVC: Execute Razor from Db String
Transpose Efficiently with Proc SQL
Efficient Query to Split a Delimited Column into Separate Rows in Another Table
Postgres SQL 'Could Not Determine Data Type of Parameter' by Hibernate
Postgres: What Is the Query 'Select * from User' Actually Doing
How to Find the Number of Days Between Two Dates