CODEWITHSUNDEEP
sqlsql-server
Casey Crookston
Casey Crookston

Reputation: 13965

Using IsNull() in a WHERE clause

This query:

SELECT
    sc.ContactID
    , c.Price
    , p.ParkID
    FROM
        tblc c
        JOIN tblsc ON c.ID= sc.ID
        LEFT JOIN tblp p ON sc.ID= p.ID
    WHERE
        (stuff = stuff)
        AND (stuff = stuff)

Returns this:

ContactID    LeadSalePrice     ParkID
-------------------------------------
    1           50             NULL
    2           60              22

Now, I am trying to set up another AND clause for ParkID. But, I only want to execute this AND on rows where ParkID IS NOT NULL. So, if on rows where ParkID IS NULL, we want to always keep those rows. On rows where ParkID IS NOT NULL, we only want to keep those rows if ParkID matches the parameter.

If @ParkID = 22, then return both rows.

If @ParkID <> 22, then only return the top row.

If @ParkID = NULL, then return both rows.

I've tried this:

SELECT
    sc.ContactID
    , c.Price
    , p.ParkID
FROM
    tblc c
    JOIN tblsc ON c.ID= sc.ID
    LEFT JOIN tblp p ON sc.ID= p.ID
    WHERE 
        (stuff = stuff)
        AND (stuff = stuff)
        AND p.ParkID = 
            CASE WHEN p.ParkID IS NULL THEN 
                NULL
           ELSE
                @ParkID
            END

This doesn't work because the proper way to compare with null is not:

= NULL

It's

IS NULL

For the same reason (I assume), this doesn't work either:

AND p.ParkID = Isnull(p.ParkID, @ParkID)

How do I do this?

Upvotes: 3

Views: 10232

Answers (2)

B H
B H

Reputation: 1876

More compact:

WHERE p.ParkID IN ('', NULL)

Upvotes: 1

Ren&#233; Vogt
Ren&#233; Vogt

Reputation: 43936

Simply check if p.ParkID is NULL or it matches @ParkID:

WHERE p.ParkID IS NULL
   OR p.ParkID = @ParkID

Upvotes: 6

Related Questions