Posted by Erland Sommarskog on 04/14/07 10:42

Pacific Fox (tacofleur@gmail.com) writes:
> I am using a dynamic order by statement;
>
> ORDER BY CASE @sort
> WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
> 0 ) AS CHAR( 5 ) )
> WHEN 1 THEN C.title
> WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
> ( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) AS
> CHAR( 9 ) )
> WHEN 3 THEN ( C.locality + ' ' + C.state )
> WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC
>
> The problem is with the numeric values, I have to cast them as a
> string, but in the results 114km
> obviously is not between 1137km and 1144km.

I saw that you resolved the problem, but wanted alternative solutions.

One is to do:

ORDER BY CASE @Sort
WHEN 0 THEN coalesce(t2. ....
WHEN 2 THEN ceiling ...
WHEN 4 THEN C.Price
END,
CASE @Sort
WHEN 1 THEN C.Title
WHEN 3 THEN C.locality ...
END

That is, have one case expression per type. If Price is not integer,
but decimal or float, that should maybe be a third branch, to avoid
conversion for the integer choices.

I can't say off-hand how this will work performancewise.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]