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:

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

But I'm afraid I'd have to settle for either two separate procs or else something like:

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_CONDITIONS AND
            [condition1]
    END
    ELSE
    BEGIN
        SELECT
            SOME_FIELDS,
            FOO,
            BAR,
            MORE_FIELDS
        FROM
            MY_TABLES
        WHERE
            A_LOT_OF_CONDITIONS AND
            [condition2]
    END
END
GO
 

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