March 9, 2015 at 8:58 am
I want to change my order by... @Sort_Option... Is there any way to do this .... except by Dynamic SQL?
March 9, 2015 at 9:30 am
You can use variations of this:
SELECT SomeColumn
FROM SomeTable
ORDER BY
CASE @Sort_Option
WHEN 'DESC' THEN NULL
ELSE ASortColumn
END ASC,
CASE @Sort_Option
WHEN 'ASC' THEN NULL
ELSE ASortColumn
END DESC
-- Gianluca Sartori
March 11, 2015 at 7:00 am
Thanks... did not know you could put case into ORDER BY ... it is a good day when you learn something new..
March 11, 2015 at 7:25 am
For some reason it is trying to convert my data type to a number... one is varchar and the second is an ID field.
, CASE @Sort_Sel
WHEN 'Resource' THEN R.RESCODE
ELSE D3.DATEID
END ASC
If I am in a case statement.. why is it trying to make the two the same data type?
March 11, 2015 at 7:53 am
dwilliscp (3/11/2015)
For some reason it is trying to convert my data type to a number... one is varchar and the second is an ID field., CASE @Sort_Sel
WHEN 'Resource' THEN R.RESCODE
ELSE D3.DATEID
END ASC
If I am in a case statement.. why is it trying to make the two the same data type?
Because the two should be the same data type.
CASE figures out what type it is returning before the query is executed. It is not expecting to get different types for different rows. If thats what you give it, it will try to implicitly convert one type to the other.
March 11, 2015 at 9:51 am
CASE @Sort_Sel
WHEN 'Resource' THEN R.RESCODE
ELSE RIGHT(REPLICATE('0', 10) + CAST(D3.DATEID AS varchar(10)), 10)
END ASC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 11, 2015 at 10:40 am
CASE figures out what type it is returning before the query is executed. It is not expecting to get different types for different rows. If thats what you give it, it will try to implicitly convert one type to the other.
Ahhhh thanks for explaining it. Why it was doing that... just did not make sense.
David
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply