January 28, 2008 at 11:26 am
I need to dynamically determine which field that I am going to sort a resultset on.
Currently I pass a parameter to my stored procedures called @SortExpression. This contains the name of the field to be sorted and the sort direction. Using the parameter @SortExpression I build a dynamic string that is used as my Select statement. I then execute this dynamic string in order to return the resultset sorted the way I would like.
Is there a better way to do this? The stored procedure that I am currently working on needs a 7 table join and I am afraid that it will run very slowly once my data volume increases.
Thanks
January 28, 2008 at 11:41 am
Not really, but you should execute the dynamic sql using the sp_executesql function, as it allows query plan reuse and functionality for parameters.
BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a8d68d72-0f4d-4ecb-ae86-1235b962f646.htm
January 28, 2008 at 1:59 pm
You can do something like this:
Use AdventureWorks
GO
Declare @OrderBy tinyint
Set @OrderBy = 1
SELECT [ContactID]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailAddress]
,[EmailPromotion]
,[Phone]
,[PasswordHash]
,[PasswordSalt]
,[AdditionalContactInfo]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[Contact]
Order By Case When @OrderBy = 1 Then LastName
When @OrderBy = 2 Then FirstName
End ,
Case When @OrderBy = 1 Then Firstname
When @OrderBy = 2 Then LastName
End
But it has some limitations like the columns you are sorting have to be the same data type.
January 28, 2008 at 2:53 pm
Dynamic SQL is usually the easiest solution for this, unless you have a very limited set of options. Using a CASE statement in your Order By clause works, but it turns into a serious maintenance/code nightmare as more options are added. It also doesn't use indexes well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2008 at 6:13 am
I tried to get the case statement to work, but I could not get the sort Descending to compile no matter what I tried. I came across this article (http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/) and then found that others had problems with sorting descending as well.
Any suggestions would be appreciated.
Thanks
January 29, 2008 at 7:20 am
I definitely think dynamic SQL is your best option here.
If you really, really don't want to do that, and have only a limited number of sort options, then If...Else might work:
if @Order = 1
Select columns ...
From tables
Where x = y
Order by X, Y
else if @Order = 2
Select columns ...
From tables
Where x = y
Order by Y, X
... and so on ...
Again, that only works if you only have a few different options, and it won't optimize well as a proc, but it will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2008 at 8:12 am
Thanks. I have decided to take your advice and go with Dynamic SQL as in this case it is much cleaner and easy to maintain. If I hit a performance bottleneck in the future I will revisit this topic.
Thanks for everyones help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply