CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

WHERE statement based on CASE statement

I am trying to add a CASE statement based on the value of a variable:

WHERE DTH.PointPerson LIKE @userParam
    AND ActivityDate >= @startDateParam
    AND ActivityDate <= @endDateParam

    --new section below
    AND CASE (@orgTeamPK)
    WHEN '%' THEN
     (USR.[OrganizationalTeamId] LIKE @orgTeamPK OR USR.[OrganizationalTeamId] IS NULL)
     ELSE
     (USR.[OrganizationalTeamId] LIKE @orgTeamPK)

However my syntax appears to be off. How do I achieve what I am trying to do above?

Upvotes: 0

Views: 79

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

As mentioned by @Anup, you don't need a case statement, but imho using case makes the syntax more clear... Each piece in a where clause must be a "PREDICATE", i.e., it must evaluate to a Boolean. The case statement in your where clause does not evaluate to a Boolean. To fix, Change it as follows:

 WHERE DTH.PointPerson LIKE @userParam
   AND ActivityDate >= @startDateParam
   AND ActivityDate <= @endDateParam

   --new section below
   AND USR.OrganizationalTeamId Like 
        CASE (@orgTeamPK) WHEN '%' THEN coalesce(@orgTeamPK, USR.OrganizationalTeamId)
              ELSE @orgTeamPK end

Upvotes: 1

Anup Shah
Anup Shah

Reputation: 1254

you do not need case stmt.

    WHERE DTH.PointPerson LIKE @userParam
        AND ActivityDate >= @startDateParam
        AND ActivityDate <= @endDateParam
        AND (
            (@orgTeamPK = '%' AND (USR.[OrganizationalTeamId] LIKE @orgTeamPK OR USR.[OrganizationalTeamId] IS NULL) )
            OR
            (@orgTeamPK <> '%' AND (USR.[OrganizationalTeamId] LIKE @orgTeamPK ) )

Upvotes: 1

Related Questions