By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Conditionals in a WHERE clause?

prn
Expert 100+
P: 254
Hi folks,

This may sound odd, but I'm preparing a stored procedure that I want to handle a couple of different cases. However, unlike the standard sort of situation where what I want to differentiate is the result, i.e., the selections, what I want to cover is the selection conditions.

I have basically two queries that differ only in a single condition, but I'd like to put them into a single stored proc and parameterize them (much more maintainable). I've boiled this down to the bare essentials, ignoring all the selected fields and the joined tables. The only difference is whether I use [condition1] or [condition2] in the WHERE clause. What I'd like is something like:

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE get_fubar
  2. (
  3.     @mystring VARCHAR(20),
  4.     @type CHAR(3)
  5. )
  6. AS
  7. BEGIN
  8.     SELECT
  9.         A_LOT_OF_FIELDS
  10.     FROM
  11.         MY_TABLES
  12.     WHERE
  13.         A_LOT_OF_CONDITIONS AND
  14.         CASE
  15.             WHEN @type = 'foo' THEN [condition1]
  16.             ELSE [condition2]
  17.         END
  18. END
  19. GO
  20.  
But I'm afraid I'd have to settle for either two separate procs or else something like:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE get_fubar
  2. (
  3.     @mystring VARCHAR(20),
  4.     @type CHAR(3)
  5. )
  6. AS
  7. BEGIN
  8.     IF @type = 'foo'
  9.     BEGIN
  10.         SELECT
  11.             A_LOT_OF_FIELDS
  12.         FROM
  13.             MY_TABLES
  14.         WHERE
  15.             A_LOT_OF_CONDITIONS AND
  16.             [condition1]
  17.     END
  18.     ELSE
  19.     BEGIN
  20.         SELECT
  21.             SOME_FIELDS,
  22.             FOO,
  23.             BAR,
  24.             MORE_FIELDS
  25.         FROM
  26.             MY_TABLES
  27.         WHERE
  28.             A_LOT_OF_CONDITIONS AND
  29.             [condition2]
  30.     END
  31. END
  32. GO
  33.  
which is not much more maintainable than two procs.

Is there some way to do this? Or would it screw up the server's execution plan so much that it wouldn't be worth it even if it could be done?

Thanks for any insights,
Paul
Sep 15 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
try something like this....
Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE (@type = 'foo' and zipcode = '94105')  or
  3.  (@type = 'not food' and statecode = 'NY')
  4.  
  5.  
the two condition can never be both true.

Good Luck!!!

--- CK
Sep 16 '09 #2

prn
Expert 100+
P: 254
prn
D'oh!

Thanks, CK! I guess that makes too much sense for me to have thought of it. My logic teachers might be tempted to go back and retroactively flunk me. :)

Thanks,
Paul
Sep 16 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.