SQL checking equality inside a case statement inside where clause?

November 9, 2022

Here is the code:

WHERE   1=1
    AND TU.Auction_I IN (41, 42)
    AND @StatePickupID = CASE
        WHEN @StatePickupID IS NOT NULL
            THEN (UP.TransportStateID = @StatePickupID)
    END
    AND @StateDropoffID = CASE
        WHEN @StateDropoffID IS NOT NULL
            THEN (UD.TransportStateID = @StateDropoffID)
        END

So I only want to return records where UP.TransportStateID is equal to StatePickupID if it is not null, same thing for DropoffID. But I get a syntax error message where the equals sign is and I cannot find any other way to check for equality online. Any help or advice would be much appreciated.

>Solution :

I only want to return records where UP.TransportStateID is equal to StatePickupID if it is not null

This would translate as the following predicate:

@StatePickupID IS NULL OR @StatePickupID = UP.TransportStateID

When the parameter is not null, the predicate filters on TransportStateID; when the parameter is null, the filter is a no-op.

In your where clause, for both parameters :

WHERE 1 = 1
    AND TU.Auction_I IN (41, 42)
    AND ( @StatePickupID  IS NULL OR @StatePickupID  = UP.TransportStateID )
    AND ( @StateDropoffID IS NULL OR @StateDropoffID = UD.TransportStateID )