Dynamic order by

[ammoos]

Hi,

I am using the following code for order by

SELECT * FROM TBLTEST
ORDER BY CASE WHEN @IsOrderByAccno=0 THEN testDate
	 ELSE testname
	 END

But when I set @IsOrderByAccno =0 it works fine. but when I set @IsOrderByAccno =1 then it generating an error message "Conversion failed when converting datetime from character string.". I think this is due to the use of different datatypes in the order by . Please help me..

[Dody]

I think it may be problem of dateformat use this
Set Dateformat ymd ;

[ck9663]

Just to be sure, when using CASE...WHEN expression, try to make sure that all the returned expressions have the same data type and preferably the same length.

So try:

SELECT * FROM TBLTEST
ORDER BY 
CASE
   WHEN @IsOrderByAccno=0 THEN convert(varchar(25), testDate,112) 
   ELSE testname
END

Just make sure your testname is 25-varchar length, max.

Good Luck!!!


~~ CK

[ammoos]

Thanks for your reply.. the issue solved.. I used the following sql

select * from table
order by 
case when @num=2 then col1 end, case when @num=1 
then col2 end