Null Value Column and NOT EXISTS T-sql

Question

I am trying to do the following:

IF NOT EXISTS ( 
                SELECT  *   
                FROM    [tbl_web_company] 
                WHERE   [name]          = @name
                AND     [address1]      = @address1
                AND     [address2]      = @address2
                AND     [city]          = @city
                AND     [province_id]   = @province_id
                AND     [postal_code]   = @postalcode
                AND     [contact_phone] = @phone
                AND     [contact_fax]   = @fax
                AND     [deleted]       = dbo.pvd_fn_getDeletedDate(@id, @active))
        BEGIN
            SELECT 'update'
        END
        ELSE
        BEGIN
            SELECT 'no update'
        END

Basically I am trying to check if any of the columns has changed, but I am having problems when @province_id and dbo.pvd_fn_getDeletedDate (@id, @active) are NULL in the database and both are set to NULL.

The province identifier is INT - Nullable

Deleted - Datetime - Nullable.

If the database entry is NULL for both of these values, then "update" will always be selected. This is not true because [province_id] and [deleted] are NULL.

Any suggestions for handling NULLS in this case?

Answer 1

ISNULL() ?

    SELECT  *   
                FROM    [tbl_web_company] 
                WHERE   [name]          = @name
                AND     [address1]      = @address1
                AND     [address2]      = @address2
                AND     [city]          = @city
                AND     ISNULL([province_id],99999) = ISNULL(@province_id,99999)
                AND     [postal_code]   = @postalcode
                AND     [contact_phone] = @phone
                AND     [contact_fax]   = @fax
                AND     ISNULL([deleted], '1990-01-01') = ISNULL(dbo.pvd_fn_getDeletedDate(@id, @active), '1990-01-01')
    BEGIN
        SELECT 'update'
    END
    ELSE
    BEGIN
        SELECT 'no update'
    END

ISNULL() , , , , , .

Answer 2

= NULL

Answer 3

Use "IS NULL" instead:

SELECT 'Is null' WHERE NULL = NULL

woudn't return any rows, but:

SELECT 'Is null' WHERE NULL IS NULL

will be...

Good reading about nulls here

Answer 4

Since your values ​​come from parameters and are not hardcoded, you can use the following:

...
AND ([province_id] IS NULL OR [province_id] = @province_id)
...

Use the same structure for other fields NULLABLE.