How to compare values which may both be null in T-SQL

Use ISNULL:

ISNULL(MY_FIELD1, 'NULL') = ISNULL(@IN_MY_FIELD1, 'NULL')

You can change 'NULL' to something like 'All Values' if it makes more sense to do so.

It should be noted that with two arguments, ISNULL works the same as COALESCE, which you could use if you have a few values to test (i.e.-COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')). COALESCE also returns after the first non-null, which means it's (marginally) faster if you expect MY_FIELD1 to be blank. However, I find ISNULL much more readable, so that's why I used it, here.

Along the same lines as @Eric's answer, but without using a 'NULL' symbol.

(Field1 = Field2) OR (ISNULL(Field1, Field2) IS NULL)

This will be true only if both values are non-NULL, and equal each other, or both values are NULL

Use INTERSECT operator.

It's NULL-sensitive and efficient if you have a composite index on all your fields:

IF      EXISTS
        (
        SELECT  MY_FIELD1, MY_FIELD2, MY_FIELD3, MY_FIELD4, MY_FIELD5, MY_FIELD6
        FROM    MY_TABLE
        INTERSECT
        SELECT  @IN_MY_FIELD1, @IN_MY_FIELD2, @IN_MY_FIELD3, @IN_MY_FIELD4, @IN_MY_FIELD5, @IN_MY_FIELD6
        )
BEGIN
        goto on_duplicate
END

Note that if you create a UNIQUE index on your fields, your life will be much simpler.

I needed a similar comparison when doing a MERGE:

WHEN MATCHED AND (Target.Field1 <> Source.Field1 OR ...)

The additional checks are to avoid updating rows where all the columns are already the same. For my purposes I wanted NULL <> anyValue to be True, and NULL <> NULL to be False.

The solution evolved as follows:

First attempt:

WHEN MATCHED AND
(
    (
        -- Neither is null, values are not equal
        Target.Field1 IS NOT NULL
            AND Source.Field1 IS NOT NULL
            AND Target.Field1 <> Source.Field1
    )
    OR
    (
        -- Target is null but source is not
        Target.Field1 IS NULL
            AND Source.Field1 IS NOT NULL
    )
    OR
    (
        -- Source is null but target is not
        Target.Field1 IS NOT NULL
            AND Source.Field1 IS NULL
    )

    -- OR ... Repeat for other columns
)

Second attempt:

WHEN MATCHED AND
(
    -- Neither is null, values are not equal
    NOT (Target.Field1 IS NULL OR Source.Field1 IS NULL)
        AND Target.Field1 <> Source.Field1

    -- Source xor target is null
    OR (Target.Field1 IS NULL OR Source.Field1 IS NULL)
        AND NOT (Target.Field1 IS NULL AND Source.Field1 IS NULL)

    -- OR ... Repeat for other columns
)

Third attempt (inspired by @THEn's answer):

WHEN MATCHED AND
(

    ISNULL(
        NULLIF(Target.Field1, Source.Field1),
        NULLIF(Source.Field1, Target.Field1)
    ) IS NOT NULL

    -- OR ... Repeat for other columns
)

The same ISNULL/NULLIF logic can be used to test equality and inequality:

• Equality: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL
• Inequaltiy: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NOT NULL

Here is an SQL-Fiddle demonstrating how it works http://sqlfiddle.com/#!3/471d60/1