使用OPENJSON插入时MyDateTime列默认值未生效问题求助

核心原因 #

存储过程中通过OPENJSON插入时MyDateTime默认值不生效，本质是INSERT语句显式指定了该列，且该列的取值为NULL（来自JSON数据缺失或JSON中的null值），而手动执行INSERT时未指定该列，触发了默认值机制。

具体场景验证 #

1. INSERT语句列列表包含MyDateTime #

如果存储过程的INSERT语句显式列出MyDateTime，并从OPENJSON结果中取值，当JSON数据中无该字段时，JSON_VALUE会返回NULL，直接插入NULL而非默认值：

-- 错误示例：显式指定MyDateTime，导致默认值失效
CREATE PROCEDURE [ual].[AddUserId]
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO [dbo].[MyTable] (UserId, MyDateTime)
    SELECT 
        JSON_VALUE(j.value, '$.UserId') AS UserId,
        JSON_VALUE(j.value, '$.MyDateTime') AS MyDateTime -- JSON无此字段时返回NULL
    FROM OPENJSON(@JsonData) j
END

2. OPENJSON的schema包含MyDateTime字段 #

若使用带WITH子句的OPENJSON定义了MyDateTime字段，即使JSON中无该值，结果集也会返回NULL，插入时覆盖默认值：

-- 错误示例：OPENJSON WITH定义了MyDateTime，返回NULL
CREATE PROCEDURE [ual].[AddUserId]
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO [dbo].[MyTable] (UserId, MyDateTime)
    SELECT UserId, MyDateTime
    FROM OPENJSON(@JsonData)
    WITH (
        UserId INT '$.UserId',
        MyDateTime DATETIME2 '$.MyDateTime' -- JSON无此字段时返回NULL
    )
END

解决方法 #

根据需求选择以下任意一种方式：

• 省略MyDateTime列：从INSERT的列列表中移除该字段，让SQL Server自动使用默认值：

CREATE PROCEDURE [ual].[AddUserId]
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO [dbo].[MyTable] (UserId) -- 不包含MyDateTime
    SELECT 
        JSON_VALUE(j.value, '$.UserId') AS UserId
    FROM OPENJSON(@JsonData) j
END

• 使用DEFAULT关键字：显式指定使用列的默认值，适用于需要保留列列表的场景：

CREATE PROCEDURE [ual].[AddUserId]
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO [dbo].[MyTable] (UserId, MyDateTime)
    SELECT 
        JSON_VALUE(j.value, '$.UserId') AS UserId,
        DEFAULT -- 直接调用默认值
    FROM OPENJSON(@JsonData) j
END

• NULL值替换为默认值：如果JSON中可能存在MyDateTime字段（有时有值，有时无），用COALESCE替换NULL为默认值：

CREATE PROCEDURE [ual].[AddUserId]
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO [dbo].[MyTable] (UserId, MyDateTime)
    SELECT 
        JSON_VALUE(j.value, '$.UserId') AS UserId,
        COALESCE(JSON_VALUE(j.value, '$.MyDateTime'), DEFAULT)
    FROM OPENJSON(@JsonData) j
END

内容的提问来源于stack exchange，提问作者Wilfred Damhuis