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.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-04-04 : 00:43:49
|
| I have a stored procedure.. If I have a ORDER BY clause and the value of the column is passed to the stored procedure (dynamic order by) .. Will that effect the efficiency of my stored procedure?Should I have two different SP's - 1 for each order by for maximum performance??ThanksMike |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-04 : 00:47:46
|
| yeah it will if you are using Dynamic Sql as it doesnt makes use of chache plan. there by taking the same time for every execution (otherwise Sql server stores the execution plan on its first exection and reuses it)you are better off using 2 sp's.-------------------------------------------------------------- |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-04 : 00:50:49
|
| No Nazim that is not what he asked. He is talking about a dynamic order clause, like in Garth's articlehttp://www.sqlteam.com/item.asp?ItemID=2209I wouldn't think it would have enough of a negative effect to warrant a duplicate proc. But I don't have time right now to run any tests on it.Damian |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-04-04 : 00:52:15
|
| Actually come to think of it, I do not know if it qualifies as dynamic SQL... this is what it will look likeCREATE PROCEDURE select_names ( @names varchar(50) @orderby varchar(20) )ASSELECT nameOnline, userID FROM tblUserDetails WHERE nameline=@names ORDER BY @orderby DESCGOthanks once again |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-04 : 00:54:50
|
| That won't work. To do that you will need dynamic sql.Read the article I linked, that will give you some ideas.Damian |
|
|
|
dsdeming
479 Posts |
Posted - 2002-04-04 : 07:53:53
|
| Could you use a CASE structure in your ORDER BY rather than dynamic SQL? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-04 : 08:17:27
|
quote:
Could you use a CASE structure in your ORDER BY rather than dynamic SQL?
someone didn't read the article  Damian |
|
|
|
|
|
|
dynamic orderby clause