Hi,
Was wondering if somebody could tell me if it is possible to use a CASE
statement within in the WHERE clause.

I'm looking to create a store procedure that can a number of parameters.
Then depending on the parameters that were passed, define the correct WHERE
clause.

Many thanks..

Wayne

CASE expressions in WHERE clauses can typically be collapsed to OR
predicates:

select
    *
from
    MyTable
where
       (@MyParm = 1)
or    (@MyParm = 2 and MyCol in (1, 2, 3))
or    (@MyParm = 3 and MyDate > '2005-07-01')

Was wondering if somebody could tell me if it is possible to use a CASE
statement within in the WHERE clause.

I'm looking to create a store procedure that can a number of parameters.
Then depending on the parameters that were passed, define the correct WHERE
clause.

Many thanks..

Wayne

Sorry I don't think that I explained it very well, I was hoping to use the
CASE statement like this...

SELECT * FROM Table WHERE
CASE @param1
    WHEN "1" THEN field1 = something
    WHEN "2" THEN field2 = something
END

See http://www.sommarskog.se/dyn-search.html for an excellent discussion of
your options.

Show quoteHide quote

You can still use the OR predicates:

SELECT *
FROM Table
WHERE
      (@param1 = "1" AND field1 = something)
OR  (@param1 = "2" AND field2 = something)


Sorry I don't think that I explained it very well, I was hoping to use the
CASE statement like this...

SELECT * FROM Table WHERE
CASE @param1
    WHEN "1" THEN field1 = something
    WHEN "2" THEN field2 = something
END

You do not know what an expression is and that is a more basic
programming issue than just SQL.  Expressions return values; they do
not change the flow of control.  Also, SQL uses single quotes for
strings and double quotes for identifiers.  I am also bothered by the
use of the term "field' in a name; it implies that you do not know that
columns are not fields and still stuck in a procedural code mindset:

Try somethng like this:

SELECT *
  FROM Fooibar
  WHERE
CASE WHEN @param1 = 1 AND field1 = something
          THEN 'T'
          WHEN @param1 = 2 AND field1 = something
          THEN 'T'
           ELSE 'F' END = 'T';



    WHEN "2" THEN field2 = something
END

On 1 Jul 2005 09:18:12 -0700, --CELKO-- wrote:

Wow, that's awfully mild rebuke by Celko standards. Feeling generous today?
Show quoteHide quote

Tired; I'll do better later.

It's hard work saving the world, one query at a time :)

Show quoteHide quote

Case Statement?
View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
Query urgent