Can you use CASE in a WHERE clause?

• manojkanoi

  SSCrazy

  Points: 2292

  More actions

  February 25, 2005 at 10:03 am

  #544198

  try using the following statement

  SELECT blah, blah FROM blah (CASE WHEN @ID > 0 THEN WHERE pkID=@ID END)

  Could someone tell me if we can have more than 1 case in a case statement. Like

  IF @ID > 0 THEN

  Use a WHERE clause like "WHERE pkID=@ID"

  ELSEIF @ID > 4

  Don't use a WHERE clause, pull back all rows

  Else

  Do something else

  END

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  February 25, 2005 at 10:29 am

  #544212

  Yes, you can use CASE in a WHERE clause:

  SELECT *

  FROM YourTable

  WHERE pkID =

    CASE @ID

      WHEN 0 THEN pkID

      ELSE @ID

    END

   

  This:

  >>try using the following statement

  >>SELECT blah, blah FROM blah (CASE WHEN @ID > 0 THEN WHERE pkID=@ID END)

  ... doesn't work, it's an example of trying to use dynamic SQL.

• Charles Cherry

  Mr or Mrs. 500

  Points: 559

  More actions

  February 25, 2005 at 11:13 am

  #544222

  Thanks!