1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
CrankySwan
  • 31
  • 6
  • 1
    GMB's answer is right. Note that what you are trying to do here, if it worked, is: `@StatePickupID = CASE WHEN @StatePickupID IS NOT NULL THEN (UP.TransportStateID = @StatePickupID)` => `@StatePickupID = UP.TransportStateID = @StatePickupID`, which would make no sense. Besides, if the `WHEN` condition is false, the `CASE` returns `NULL`, so the equality will be false and the query will return nothing as all conditions are joined with `AND`. – Andrew Nov 09 '22 at 20:56

1 Answers1

4

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 )
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 3
    Considering what @Shawn tried, I think it's worth highlighting that those parenthesis are **mandatory** for the logic to work. – Andrew Nov 09 '22 at 20:59