Posted by Ed Murphy on 04/16/07 03:38

Pacific Fox wrote:

> 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.

Adapted from a workaround recently posted by Erland:

ORDER BY
CASE @sort WHEN 0 THEN t2.RANK END,
CASE @sort WHEN 0 THEN t3.RANK END,
CASE @sort WHEN 1 THEN C.title END,
CASE @sort WHEN 2 THEN CEILING( [dbo].[fn_calculateDistance]
( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) END,
CASE @sort WHEN 3 THEN C.locality + ' ' + C.state END,
CASE @sort WHEN 4 THEN C.price END

For instance, when @sort = 4, then all formulas except
CASE @sort WHEN 4 THEN C.price END
return NULL and thus have no effect on the sort order.

[Back to original message]