CODEWITHSUNDEEP
sql-server
user
user

Reputation: 117

Where clause in sql server with multiple values in case when

I have filter in my application that has to be converted to SQL server syntax.

   COLUMN1 in (
    if {?Param} = "VALUE1" then 'V1'
    else if {?Param} = "VALUE2" then 'V2'
    else if  {?Param} = "VALUE1 AND VALUE2" THEN 'V1,V2'
   )

The first two conditions can be checked with case when but the last condition is giving error as multiple values have to be checked.

Can anyone please guide how to add this condition in where clause in SQL server.

Upvotes: 1

Views: 112

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

 WHERE (@Param = 'VALUE1' AND COLUMN1 = 'V1')
    OR (@Param = 'VALUE2' AND COLUMN1 = 'V2')
    OR (@Param = 'VALUE1 AND VALUE2' AND COLUMN1 IN ('V1', 'V2'))

or:

WHERE
  COLUMN1 = CASE @Param
              WHEN 'VALUE1' THEN 'V1'
              WHEN 'VALUE2' THEN 'V2'
            END
  OR (@Param = 'VALUE1 AND VALUE2' AND COLUMN1 IN ('V1', 'V2'))

Upvotes: 1

Related Questions