469,086 Members | 1,146 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Conditionals in a WHERE clause?

prn
254 Expert 100+
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
2 1511
ck9663
2,878 Expert 2GB
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
254 Expert 100+
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.

Similar topics

1 post views Thread by FHuang | last post: by
15 posts views Thread by Joshua Ginsberg | last post: by
1 post views Thread by Paul Dale | last post: by
3 posts views Thread by steven | last post: by
4 posts views Thread by Leon Lambert | last post: by
11 posts views Thread by .Net Sports | last post: by
21 posts views Thread by Frederick Gotham | last post: by
5 posts views Thread by Hul Tytus | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.