当目标值为 null 时，更新插入存储过程不起作用

Question

我想知道如何修复下面定义的存储过程，因为当目标值为null时它不能正常工作。使用EmailAddress中的空值，此尝试。EmailAddress & lt;比;src。EmailAddress总是解析为false，因此，如果目标值为null，则更新upsert脚本的一部分将不会运行。

是解决这个问题的最有效的方法来检查它们是否为空?如:trg.EmailAddress <> src.EmailAddress OR trg.EmailAddress IS NULL

表类型和存储过程的当前定义如下:

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

Answer 1

这里的问题是，trg.FirstName <> src.FirstName和trg.FirstName = src.FirstName将解决未知，如果trg.FirstName或src.FirstName有值NULL，这(重要的)不是真的。

在最新版本的SQL Server上，这很容易通过使用IS DISTINCT FROM来解决。对于旧版本，您需要处理NULLs。

对于不等式，你需要这样做:

--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))

对于相等性检查，应该是这样的:

--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))

这是CASE表达式，但是您可以看到这个数据库中的逻辑