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 |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2009-05-12 : 09:22:24
|
| We have several stored procs that need to make use of various different orderings of the same result sets, so we have an @OrderByClause varchar input parameter. So we make every select statement dynamic since we need to add this flexible @OrderByClause. The problem is with all these dynamic queries, anything will compile even when there are errors in the dynamic select code.Also, I don't think SQL Server can create an optimized execution plan ahead of time with Dynamic SQL.Any ideas on other options or improvements? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-12 : 09:42:35
|
| what all will be typical values passed for @OrderByClause? how about using case ...when expression rather than dynamic sql? |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2009-05-12 : 09:50:35
|
| That is the first response to a post of mine. Very exciting.The possibilities for '@OrderByClause' are endless: 'C1', 'C1, C2', 'C2, C1', 'C1, C2, C3', ...So next to impossible with Case Statement, if I understand. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-13 : 05:46:47
|
| If you use sp_executeSql and the strings for the order by are consistent (so 'C1,C2' is always passed that way) then you will be able to reuse query plans.Check out http://www.sommarskog.se/dynamic_sql.htmlCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Dynamic Order By