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.

Anyone any ideas on this?
Thanks in advance.

Got this one sorted, I am padding the string with zeros. I think it is
affecting the execution time drastically though (talking about 500,000
records). Will do some more reseach, any better suggestions would be
appreciated.

Pacific Fox (tacofleur (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thats is excellent, I'm sure it will do better, thanks for that, will
give it a go.

Cheers.

Pacific Fox wrote:

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.