Dynamic order by

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@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thats is excellent, I'm sure it will do better, thanks for that, will
give it a go.

Cheers.|||Pacific Fox wrote:

Quote:

Originally Posted by

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.

Dynamic Link server

I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
|||The problem here is, it is not accepting linked server name in the variable.
DECLARE @.Server as varchar(30)
SET @.Server = 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks