CODEWITHSUNDEEP
sqlsql-server-2005t-sqlstored-procedures
Sungguk Lim
Sungguk Lim

Reputation: 6228

where condition depending on parameter

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

Upvotes: 1

Views: 131

Answers (2)

Michael Buen
Michael Buen

Reputation: 39483

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

Upvotes: 1

Oleks
Oleks

Reputation: 32343

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 = '')
       )

Upvotes: 1

Related Questions