Dynamic Order By in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Order By in Stored Procedure

Author

Topic

zavier
Yak Posting Veteran

50 Posts

Posted - 2004-05-14 : 16:32:31

Hello All

Thanks in advance for any help anyone can give with this. I have an SP which simplified preforms the following:

select
c.fname + ' ' + c.lname as ClientName,
c.CreatedBy,
c.CreateDate

from Clients c

order by

case --ascending character fields
when @SortDir = 'asc' and @SortField = 'EmployeeName' then c.CreatedBy
when @SortDir = 'asc' and @SortField = 'ClientName' then ClientName
end asc,
case --descending character fields
when @SortDir = 'desc' and @SortField = 'EmployeeName' then c.CreatedBy
when @SortDir = 'desc' and @SortField = 'ClientName' then ClientName
end desc

The problem is that this fails when I use the alias in the order by clause. If I replace the alias with an actual field name like c.fname this query works and the sp compiles. I don't get this. If I wasn't using the order by dynamically, using an alias works but not otherwise.

Can anyone help? Thanks again.

Zavier

zavier
Yak Posting Veteran

50 Posts

Posted - 2004-05-14 : 16:53:09

If anyone is interested I found a work around for this but it is not the most elegant solution. Basically, I created a view from the select statement. I then created a SP that queried the view and then the alias fields were no longer a problem and I could order by any field I choose.

If anyone can post a way to make this work without having to create the view I would be very interested to hear it. Thanks.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-05-14 : 16:55:24

[code]
select ClientName,CreatedBy,CreateDate from (
c.fname + ' ' + c.lname as ClientName,
c.CreatedBy,
c.CreateDate

from Clients c) dt

order by

case --ascending character fields
when @SortDir = 'asc' and @SortField = 'EmployeeName' then CreatedBy
when @SortDir = 'asc' and @SortField = 'ClientName' then ClientName
end asc,
case --descending character fields
when @SortDir = 'desc' and @SortField = 'EmployeeName' then CreatedBy
when @SortDir = 'desc' and @SortField = 'ClientName' then ClientName
end desc
[/code]

zavier
Yak Posting Veteran

50 Posts

Posted - 2004-05-14 : 17:31:27

Hello LarsG, can you explain your post a little. I'm not sure I follow.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-14 : 17:37:07

He is using a derived table in the FROM so that you can reference the aliased columns. I believe it should say (SELECT c.fname...FROM Clients c) dt.

Tara

zavier
Yak Posting Veteran

50 Posts

Posted - 2004-05-14 : 17:40:17

Thanks Tara & LarsG. Works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources