sqlsql-serverwhere-clausesql-null

SQL checking equality inside a case statement inside where clause?


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 )