July 13, 2011 at 2:29 pm
I have Sort order I want to do where if a parameter is 1 then it is sorted by one column and if 2 a different column.
The problem is that 1 is by name and the other is by an order number (1,2,3). I could do a conversion on the int value but then then "11" would be before "2" which is not how it would be sorted by if by integer.
ie:
Declare @Sortorder int
SELECT Id AS PK ,
DisplayOrder ,
Name
FROM CreditCardType
ORDER BY CASE @SortOrder WHEN 1 THEN Name ELSE DisplayOrder END
This would give me an error when trying to convert the name to integer.
Thanks,
Tom
July 13, 2011 at 2:53 pm
tshad (7/13/2011)
I have Sort order I want to do where if a parameter is 1 then it is sorted by one column and if 2 a different column.The problem is that 1 is by name and the other is by an order number (1,2,3). I could do a conversion on the int value but then then "11" would be before "2" which is not how it would be sorted by if by integer.
ie:
Declare @Sortorder int
SELECT Id AS PK ,
DisplayOrder ,
Name
FROM CreditCardType
ORDER BY CASE @SortOrder WHEN 1 THEN Name ELSE DisplayOrder END
This would give me an error when trying to convert the name to integer.
Thanks,
Tom
Try:
ORDER BY CASE @SortOrder WHEN 1 THEN cast(Name as SQL_Variant) ELSE cast(DisplayOrder as SQL_Variant) END
Edited to change "SQLVariant" to "SQL_Variant"
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 13, 2011 at 3:11 pm
By the way, the solution I posted is going to take away the optimizers' opportunity to take advantage of any appropriately-sequenced covering indexes. In other words, your execution plan will always include a sort. You could avoid this by using dynamic SQL to build your ORDER BY clause based on the input parameter. More work than using a CASE expression, but potentially better performance in the long run.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply