August 25, 2009 at 6:51 am
Hi ,
I am trying dynamic order by using Case statement in Order by clause.
like
declare @sortorder int
select UserID,dbo.getUserName(UserID) as 'UserName'
from User
order by
case when @sortorder = 1 then UserID desc,
case when @sortorder = 2 then 'UserName' desc
it does not sort when i pass @sororder =2, but the same query return desired result if i use like
select UserID,dbo.getUserName(UserID) as 'UserName'
from User
order by 'UserName' desc
or
select UserID,dbo.getUserName(UserID) as 'UserName'
from User
order by
case when @sortorder = 1 then UserID desc,
case when @sortorder = 2 then dbo.getUserName(UserID) desc
I am a bit confused that why I am getting this dynamic behavior.
Please suggest.........
Thanks in advance...............
August 25, 2009 at 7:05 am
Try removing the quotes from around UserName, both in the order by and the select statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2009 at 7:50 am
Hi GilaMonster,
I tried it after removing the single quotes but now I am getting error
"Invalid column Name UserName".
🙁
August 25, 2009 at 8:00 am
Wrap it in Cte
with cteUsers(UserId,UserName)
as
(
select UserID,dbo.getUserName(UserID) as 'UserName'
from User
)
Select UserId,UserName
from cteUsers
order by
case when @sortorder = 1 then UserId end desc,
case when @sortorder = 2 then UserName end desc
August 25, 2009 at 8:02 am
Post the revised query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2009 at 8:04 am
Try this:
select UserID,dbo.getUserName(UserID) as 'UserName'
from User
order by
(case when @sortorder = 1 then UserID
when @sortorder = 2 then dbo.getUserName(UserID) END) desc
August 25, 2009 at 8:54 am
i've just tried JTSs example.
it works to a degree. but for some reason you cant do the case in the order by clause with different datatypes?
i tried
select id,firstname,surname from footballers
order by (case when @sort = 1 then ID
when @sort = 2 then surname end)
now when @sort = 2 it says
'conversion failed when converting the varchar value 'Torres' to data type int.
if @sort = 1 then it works fine.
August 25, 2009 at 9:05 am
Yes, I missed that. When I tested it, I used two varchar columns. I suppose that you could wrap a CAST around the UserID like this
select UserID, dbo.getUserName(UserID) as 'UserName'
from User
order by
(case when @sortorder = 1 then CAST(UserID AS varchar(50))
when @sortorder = 2 then dbo.getUserName(UserID) END) desc
This should work (I haven't tested it) although I can't say that it's the best way of doing what you want.
August 25, 2009 at 9:24 am
of course. just cast the id. why didn't i think of that? 😀
August 25, 2009 at 9:55 am
I just realized that there shouldn't be quotes around Username:
select UserID, dbo.getUserName(UserID) as UserName
from User
order by
(case when @sortorder = 1 then CAST(UserID AS varchar(50))
when @sortorder = 2 then dbo.getUserName(UserID) END) desc
I would also suggest to the OP to not use a scalar function to get the user name if possible. It really hits the performance of the query.
August 25, 2009 at 11:53 am
I don't think that just casting the UserID to varchar will work, because in this way UserID '2' will become before UserID '11', when sorted desc. You have to properly align the result of the cast, i.e.
right(replicate(' ', 10) + CAST(UserID AS varchar(10)), 10)
Here's my test script. Play with it.
--create table [User]
--( UserID int identity primary key )
--/* Repeat at least 11 times */
--insert into [User] default values
declare @sortorder int
select @sortorder = 2;
select
UserID, cast(UserID as varchar(10)) as UserName
from
[User]
order by
case @sortorder
when 1 then right(replicate(' ', 10) + CAST(UserID AS varchar(10)), 10)
when 2 then cast(UserID as varchar(10))
end desc
Peter
August 26, 2009 at 1:45 am
I think the topic is diverted 🙂
To avoid the casting we can use multiple case in order by which i do
case
when @sortorder = 1 then UserID END Asc,
when @sortorder 2 then UserName end desc
But I think when we use CASE in order by then the Alias does not work in that.
Please tell me if anyone have any direct solution because I dont want to hit the performance, and dont want to use CTE too.
August 26, 2009 at 2:12 am
AJ (8/26/2009)
Please tell me if anyone have any direct solution because I dont want to hit the performance, and dont want to use CTE too.
Why Dont you want to use a CTE ?. It provides the solution you need. BTW This sort of query can have a very negative impact on performance , as it makes it impossible for the optimizer to pick an optimal route under all scenarios.
August 26, 2009 at 2:38 am
Dave,
I am already using CTE to generate the RowID as per the Order by column passesd. For which I am using the Dynamic Order By.
As per my requirement I have to implement a logic in which user will pass the PageIndex, pagesize and Order by column and I have to return the No.Of records = PageSize and they must be in order of Order by column and the records will be as per the page index like if page index = 2 and page size =20 and Order by column = UserName then query will fetch the 20 records whose RowID will be from 21-40 and the RowID will be generated on Order By Column.
So for this I am already using the CTE so now again I think I cant use it.
August 26, 2009 at 2:44 am
!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply