where condition depending on parameter

Question

Hi. I'm a sql newbie, I use mssql2005

I like to do select with the condition which is depending on the input parameter

I've tried this.

 WHERE CF.PROCESS_DATE = '2010-05-05' AND 
 CASE @CATEGORY_LEVEL
    WHEN 'L' THEN CAS.MCATEGORY_ID = '' AND CAS.SCATEGORY_ID = ''
    WHEN 'M' THEN CAS.SCATEGORY_ID = ''
 END

but didn't work and happened an error.

sql is difficult to newbie programmer.. T.T

Answer 1

You could rewrite the condition as:

 WHERE CF.PROCESS_DATE = '2010-05-05' AND 
       (
           (@CATEGORY_LEVEL = 'L' AND CAS.MCATEGORY_ID = '' AND CAS.SCATEGORY_ID = '') OR
           (@CATEGORY_LEVEL = 'M' AND CAS.SCATEGORY_ID = '')
       )

Answer 2

WHERE CF.PROCESS_DATE = '2010-05-05' AND 
    (

    (@CATEGORY_LEVEL = 'L' AND CAS.MCATEGORY_ID = '' AND CAS.SCATEGORY_ID = '')

    OR

    (@CATEGORY_LEVEL = 'M' AND CAS.SCATEGORY_ID = '')

    )

(Un)fortunately, your code works on other database(e.g. Postgres, MySQL), SQL Server don't have first class support for boolean, so your expression after of THEN won't result to boolean type, hence will not work in Sql Server