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 |
|
erybarczyk
Starting Member
3 Posts |
 Posted - 2002-03-28 : 16:10:01
|
| I've got some stored procedures which operate on different tables depending on the value of a parameter passed in. I use an IF statement to evaluate the parameter value.I'm thinking this is a good reason to set WITH RECOMPILE on these sprocs, because the tables the sproc will operate on will vary as the procedure is called. I'm thinking I don't want the cached query plan, from the first call which happens to use tables A, B and C, to become THE query plan, since the next call might be using tables X, Y and Z.Am I on the right track? Thanks for any advice. - Eric |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-03-28 : 17:25:39
|
| I disagree. I think the RECOMPILE should only be used when you are using dynamic sql, because a cached query plan is never generated. There is a good concept of delegators on www.sql-server-performance.com, which should address the need for the IF statement issue. Basically it talks about a stored procedure that has an IF statement, and based on the path it takes it will call another stored procedure.*************************Just trying to get things done |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-28 : 17:44:44
|
| Maybe you could do something like this, and totally avoid this altogether?psudo codeIf myvar = 1 then exec proc1else exec proc2end ifThen the query plan for the "real" queries are cached? Guru's, am I right? |
|
|
|
erybarczyk
Starting Member
3 Posts |
Posted - 2002-03-29 : 15:53:06
|
| Thanks for the info, it makes sense. I was being lazy by combining some things. Having to think it through even to post this here made me reconsider. I'll do it the right way even if it is a little more hassle to separate the sprocs. I want the performace gain from the sproc anyway, so in reality I don't want to recompile anyway.Thanks for the feedback!- Eric |
|
|
|
|
|
|
|
|
should I use WITH RECOMPILE ??