Upsert stored procedure doesn't work when target values are null-CodePudding

I am wondering how to fix the stored procedure defined below, because it is not working correctly when the target values are null. With the null values in EmailAddress, this trg.EmailAddress <> src.EmailAddress always resolves to false, so then update part of the upsert script, won't run if the target values are null.

Is the most efficient way to resolve this to also check if they are null? Such as: trg.EmailAddress <> src.EmailAddress OR trg.EmailAddress IS NULL

Below are the current definitions for table type and stored procedure:

CREATE TYPE [dbo].[UserType] AS TABLE(
    [FirstName] [varchar](500) NULL,
    [LastName] [varchar](500) NULL,
    [FullName] [varchar](500) NULL,
    [EmailAddress] [varchar](500) NULL
)
GO

CREATE PROCEDURE [dbo].[uspMergeUser]
(
    @User [dbo].[UserType] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    
    MERGE dbo.User AS trg
    USING @User AS src
        ON (trg.UserID = src.UserID )
    WHEN MATCHED AND 
        (
            trg.FirstName <> src.FirstName
            OR trg.LastName <> src.LastName
            OR trg.FullName <> src.FullName
            OR trg.EmailAddress <> src.EmailAddress
        )
   THEN
        UPDATE SET 
            trg.[FirstName] = src.[FirstName]
            ,trg.[LastName] = src.[LastName]
            ,trg.[FullName] = src.[FullName]
            ,trg.[EmailAddress] = src.[EmailAddress]
            ,trg.[UpdatedAt] = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT 
        (
            [UserID]
            ,[FirstName]
            ,[LastName]
            ,[FullName]
            ,[EmailAddress]
        )
        VALUES 
        (
            src.UserID
            ,src.FirstName
            ,src.LastName
            ,src.FullName
            ,src.EmailAddress
        );
END

CodePudding user response：

The problem here is that trg.FirstName <> src.FirstName and trg.FirstName = src.FirstName will resolve to UNKNOWN if either trg.FirstName or src.FirstName have the value NULL, which (importantly) isn't TRUE.

On the latest version of SQL Server this is easily resolved by using IS DISTINCT FROM instead. For older versions, you would need to handle the NULLs.

So for inequality you would need to do the following:

--SQL Server 2022 
WHERE trg.FirstName IS DISTINCT FROM src.FirstName
--Older versions
WHERE (trg.FirstName <> src.FirstName
   OR  (trg.FirstName IS NULL AND src.FirstName IS NOT NULL)
   OR  (trg.FirstName IS NOT NULL AND src.FirstName IS NULL))

For equality checks, it would be like this:

--SQL Server 2022 
WHERE trg.FirstName IS NOT DISTINCT FROM src.FirstName
--Older versions
WHERE (trg.FirstName = src.FirstName
   OR  (trg.FirstName IS NULL AND src.FirstName IS NULL))

This is with CASE expressions, but you can see the logic in this db<>fiddle