CODEWITHSUNDEEP
sqlsql-server
Jason Ayer
Jason Ayer

Reputation: 597

SQL Server conditional where clause

I have the following where clause that begins with the following:

DECLARE @Birthdate = 1

SELECT ... 
FROM ... 
JOIN ...
WHERE A.organization_id = 1
  AND LEFT(A.[first_name], 3) = LEFT(B.[first_name], 3)
  AND LEFT(A.[last_name], 3) = LEFT(B.[last_name], 3)

After this I would like to conditionally execute additional clauses.

For example:

I've tried the following and none seem to work properly:

Ex 1: 

 AND (@Birthdate = 1 AND A.[birthdate] = B.[birthdate])

Ex 2: 

AND ((@Birthdate = 1 AND A.[birthdate] = B.[birthdate]) OR 
     (@Birthdate = 0 AND (A.[birthdate] = B.[birthdate] OR 
                          A.[birthdate] <> B.[birthdate])))

Any suggestions as to what I'm doing wrong?

Upvotes: 2

Views: 52

Answers (1)

RToyo
RToyo

Reputation: 2877

You can use an or to apply your filter, or to just return all rows when @Birthdate = 0

SELECT
    ...
FROM
    ...
WHERE
    (
        -- Apply the filter if applicable
        (
            @Birthdate = 1
            and A.[birthdate] = B.[birthdate]
        )
        -- Or return everything
        or @Birthdate <> 1
    )

Upvotes: 2

Related Questions