Dynamic Order By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Order By

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-10 : 08:52:40

Randy writes "I'm running on Windows XP. My SQL Server is running Server 2000 with SQL Server 7.0.

I have a SP as follows:
----------------------------------------------
CREATE PROCEDURE Get_Drawings
@Dwg_no varchar(50) = Null,
@SortField varchar(50),
@SortDirection varchar(4)

AS

SELECT Dwg_no, Plat_no, Std_type, Out_Date
FROM
tblDrawings
WHERE
Dwg_no = COALESCE(@Dwg_no, Dwg_no)
ORDER BY
CASE
WHEN @SortField = 'Dwg_no' THEN Dwg_no
WHEN @SortField = 'Plat_no' THEN Plat_no
WHEN @SortField = 'Std_type' THEN Std_type
WHEN @SortField = 'Out_date' THEN Out_Date
END
----------------------------------------------
After reading many of your great articles on this site, I got the Order By Clause to use the field value from @sortfield. I have been wrestling with trying to get the Order By direction to be dynamic as well, without any luck. I want to pass the direction (Asc, or Desc) to @sortDirection and then use it.

Do you know if this can be done? Any Suggestions?

Thank You

Randy Pagels
PagelsR@ComCast.Net"

dsdeming

479 Posts

Posted - 2002-04-10 : 09:20:12

Have you tried something like this:

WHEN @SortField = 'Plat_no' AND @sortDirection = 'ASC' THEN Plat_no
WHEN @SortField = 'Plat_no' AND @sortDirection = 'DESC' THEN Plat_no DESC
etc...

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-04-10 : 16:03:41

Standard SQL-92 does not allow you to use a function in an ORDER BY clause. The ORDER BY clause is part of a cursor and it can only see the column names that appear in the SELECT clause list that was used to build the result set. BP will now chime in that SQL-99 (officially called "a standard in progress" and not recognized by the U.S. Government for actual use) does allow this.

But aside from this, there is the good programming practice of showing the fields that are used for the sort to the user, usually on the left side of each line since we read left to right.

The standard trick for picking a sorting order at run time is to use a flag in CASE expression. If you want to sort on more than one column and allow all possible combinations of sorting use one CASE per column:

SELECT
CASE @flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
...
CASE @flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,

FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...

More than one sort column and only a limited set of combinations then use concatenation.

CASE @flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,

If you need ASC and DESC options, then use a combination of CASE and ORDER BY

CASE @flag_1
WHEN @flag_1 = 'a' AND @flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @flag_1 = 'b' AND @flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @flag_1 = 'c' AND @flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @flag_1
WHEN @flag_1 = 'a' AND @flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @flag_1 = 'b' AND @flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @flag_1 = 'c' AND @flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d

.. ORDER BY sort_1_a ASC, sort_1_d DESC

I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of the THEN clause expressions were already the same, there would be no reason to force the conversions.

You change the ELSE NULL clause to any constant of the appropriate datatype, but it should be something useful to the reader.

A neater way of doing this is to use one column for each sorting option so you do not have worry about CAST() operations.

SELECT ...
CASE WHEN @flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources