Conditionals in a WHERE clause?

[prn]

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:

[code=sql]
CREATE PROCEDURE get_fubar
(
@mystring VARCHAR(20),
@type CHAR(3)
)
AS
BEGIN
SELECT
A_LOT_OF_FIELDS
FROM
MY_TABLES
WHERE
A_LOT_OF_CONDIT IONS AND
CASE
WHEN @type = 'foo' THEN [condition1]
ELSE [condition2]
END
END
GO
[/code]

But I'm afraid I'd have to settle for either two separate procs or else something like:
[code=sql]
CREATE PROCEDURE get_fubar
(
@mystring VARCHAR(20),
@type CHAR(3)
)
AS
BEGIN
IF @type = 'foo'
BEGIN
SELECT
A_LOT_OF_FIELDS
FROM
MY_TABLES
WHERE
A_LOT_OF_CONDIT IONS AND
[condition1]
END
ELSE
BEGIN
SELECT
SOME_FIELDS,
FOO,
BAR,
MORE_FIELDS
FROM
MY_TABLES
WHERE
A_LOT_OF_CONDIT IONS AND
[condition2]
END
END
GO
[/code]

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

[ck9663]

try something like this....

WHERE (@type = 'foo' and zipcode = '94105')  or
 (@type = 'not food' and statecode = 'NY')

the two condition can never be both true.

Good Luck!!!

--- CK

[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