October 3, 2002 at 3:47 pm
Hi,
I have a rather complicated stored procedure which revolves around a single nested SELECT command, and this works a treat.
However, I want to be able to pass the 'WHERE' clause as a parameter to the sp and incorporate it into the SELECT command...
something along the lines of...
SELECT * FROM MyTable WHERE @myWhereClause
(The @myWhereClause parameter could filter on one of many fields in the table, with different ranges of values)
Is this possible?
If not, the only way I can think of achieving what I want to do, is to have a separate SELECT command for each combination of fields I want to filter on.
Any suggestions gratefully received...
October 3, 2002 at 3:59 pm
Hi,
We have had similar requests from our developers. We worked on creating where clauses using the case statement in the where clause based on a parameter code passed into the stored procedure. The ranges were passed in also and set as variables. This approach has worked well for us. If you'd like a sample I'd be glad to dig one out for you.
Jody
October 3, 2002 at 4:06 pm
Goughj's answer is the only way I know to do what you are asking. However, you may want to break it down a bit further than that for a very simple reason.
One would assume that your different where clauses could include references to different columns. If so, the Query Plan will be chosen based on the where clause run the first time, which is very unlikely to be the optimum for the other where clauses.
The answer is simple. Simply use the one master SP, and let it call other SP's that are each optimized on their own. This way you can share as much code as possible, and still have good performance.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
October 3, 2002 at 4:06 pm
Thanx for that.
I must admit that the same idea had occurred to me, but shyed away from trying it as the tables concerned are HUGE and I had doubts that SQL would be able to optimize with indexes by taking this approach...
Can you confirm whether this is the case or not?
October 3, 2002 at 4:06 pm
We've had a few threads and some articles on this. In particular Robert Marda has written about it quite a bit, here is a good place to start:
http://www.sqlservercentral.com/columnists/rmarda/dynamicsqlversusstaticsqlp1.asp
Andy
October 4, 2002 at 6:06 am
Thanx, folks, for your advice...
I finally cracked it using Dynamic SQL .. works a treat.
Thanx again.
Joe
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply