SQL Server存储过程条件更新：如何支持字段设为NULL？

现有SQL Server存储过程如下：

CREATE PROCEDURE UPDATE_CONTRACT
  @contractId     UNIQUEIDENTIFIER,
  @name           VARCHAR(256) = NULL,
  @description    VARCHAR(MAX) = NULL,
  @effectiveDate  DATETIME2 = NULL,
  @expirationDate DATETIME2 = NULL
AS
BEGIN
  UPDATE CONTRACT
  SET    NAME = ISNULL(@name, NAME),
         DESCRIPTION = ISNULL(@description, DESCRIPTION),
         EFFECTIVE_DATE = ISNULL(@effectiveDate, EFFECTIVE_DATE),
         EXPIRATION_DATE = ISNULL(@expirationDate, EXPIRATION_DATE)
  WHERE  CONTRACT_ID = @contractId
END
GO

该存储过程支持选择性更新字段，但存在核心问题：无法直接将expirationDate设为NULL——未传参时参数默认值为NULL，无法区分是「不更新该字段」还是「要把该字段设为NULL」。

当前尝试的两种方案均有缺陷：新增@noExpiration这类标记参数，多字段需设NULL时会非常繁琐；拆分单字段更新语句，代码冗余且不简洁。

补充场景：该存储过程由使用mssql库的Express应用调用，前端仅传入需更新的字段，现有调用逻辑如下：

router.patch('/updateContract', (req, res) => {
  const {
    contractId,
    name,
    description,
    effectiveDate,
    expirationDate
  } = req.body;

  const request = new sql.Request();

  request.input('contractId', sql.Int, contractId);
  
  if (name)
    request.input('name', sql.VarChar, name);

  if (description)
    request.input('description', sql.VarChar, description);

  if (effectiveDate)
    request.input('effectiveDate', sql.DateTime, effectiveDate);

  if (expirationDate)
    request.input('expirationDate', sql.DateTime, expirationDate);

  request.execute('UPDATE_CONTRACT', (err, result) => {
    // 业务处理逻辑
  });
});

若要将expirationDate设为NULL，需额外增加判断：

if (expirationDate === null)
  request.input('noExpiration', sql.Int, 1);
else if (expirationDate)
  request.input('expirationDate', sql.DateTime, expirationDate);

方案1：字段更新标记参数（通用型） #

为每个需支持设NULL的字段新增布尔型标记参数，明确标记是否要修改该字段（包括设为NULL），可批量扩展，逻辑清晰。

存储过程修改 #

CREATE PROCEDURE UPDATE_CONTRACT
  @contractId           UNIQUEIDENTIFIER,
  -- 字段参数
  @name                 VARCHAR(256) = NULL,
  @description          VARCHAR(MAX) = NULL,
  @effectiveDate        DATETIME2 = NULL,
  @expirationDate       DATETIME2 = NULL,
  -- 更新标记：1=修改该字段，0=不修改（默认）
  @updateName           BIT = 0,
  @updateDescription    BIT = 0,
  @updateEffectiveDate  BIT = 0,
  @updateExpirationDate BIT = 0
AS
BEGIN
  UPDATE CONTRACT
  SET 
    NAME = CASE WHEN @updateName = 1 THEN @name ELSE NAME END,
    DESCRIPTION = CASE WHEN @updateDescription = 1 THEN @description ELSE DESCRIPTION END,
    EFFECTIVE_DATE = CASE WHEN @updateEffectiveDate = 1 THEN @effectiveDate ELSE EFFECTIVE_DATE END,
    EXPIRATION_DATE = CASE WHEN @updateExpirationDate = 1 THEN @expirationDate ELSE EXPIRATION_DATE END
  WHERE CONTRACT_ID = @contractId
END
GO

Node.js调用优化 #

统一处理所有字段，只要前端传入该字段（包括值为null），就传入字段值并标记更新：

router.patch('/updateContract', (req, res) => {
  const { contractId, ...updateFields } = req.body;
  const request = new sql.Request();

  // 必传参数
  request.input('contractId', sql.UniqueIdentifier, contractId);

  // 字段映射：关联字段名、SQL类型、更新标记参数
  const fieldMap = {
    name: { sqlType: sql.VarChar, updateFlag: 'updateName' },
    description: { sqlType: sql.VarChar, updateFlag: 'updateDescription' },
    effectiveDate: { sqlType: sql.DateTime, updateFlag: 'updateEffectiveDate' },
    expirationDate: { sqlType: sql.DateTime, updateFlag: 'updateExpirationDate' }
  };

  Object.entries(fieldMap).forEach(([fieldName, config]) => {
    if (updateFields.hasOwnProperty(fieldName)) {
      // 不管值是null还是有效内容，都传入字段值
      request.input(fieldName, config.sqlType, updateFields[fieldName]);
      // 标记要更新该字段
      request.input(config.updateFlag, sql.Bit, 1);
    }
  });

  request.execute('UPDATE_CONTRACT', (err, result) => {
    // 业务处理逻辑
  });
});

方案2：JSON参数传递（极简扩展型） #

将所有更新字段打包成JSON参数传入，存储过程解析JSON后更新对应字段，无需为每个字段单独定义参数，扩展性极强。

存储过程修改（静态字段版） #

CREATE PROCEDURE UPDATE_CONTRACT
  @contractId UNIQUEIDENTIFIER,
  @updateData NVARCHAR(MAX) -- JSON格式的更新字段
AS
BEGIN
  SET NOCOUNT ON;
  UPDATE CONTRACT
  SET
    NAME = ISNULL(JSON_VALUE(@updateData, '$.name'), NAME),
    DESCRIPTION = ISNULL(JSON_VALUE(@updateData, '$.description'), DESCRIPTION),
    EFFECTIVE_DATE = ISNULL(TRY_CAST(JSON_VALUE(@updateData, '$.effectiveDate') AS DATETIME2), EFFECTIVE_DATE),
    EXPIRATION_DATE = ISNULL(TRY_CAST(JSON_VALUE(@updateData, '$.expirationDate') AS DATETIME2), EXPIRATION_DATE)
  WHERE CONTRACT_ID = @contractId;
END
GO

存储过程修改（动态扩展版） #

支持后续新增字段无需修改存储过程：

CREATE PROCEDURE UPDATE_CONTRACT
  @contractId UNIQUEIDENTIFIER,
  @updateData NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX) = N'UPDATE CONTRACT SET ';
  DECLARE @params NVARCHAR(MAX) = N'@contractId UNIQUEIDENTIFIER, @updateData NVARCHAR(MAX)';

  -- 解析JSON键值对，生成动态SET语句
  SELECT @sql += QUOTENAME([key]) + N' = ISNULL(TRY_CAST(JSON_VALUE(@updateData, ''$.' + [key] + N''') AS ' + 
    CASE [key]
      WHEN 'name' THEN N'VARCHAR(256)'
      WHEN 'description' THEN N'VARCHAR(MAX)'
      WHEN 'effectiveDate' THEN N'DATETIME2'
      WHEN 'expirationDate' THEN N'DATETIME2'
    END + N'), ' + QUOTENAME([key]) + N'), '
  FROM OPENJSON(@updateData);

  -- 移除末尾多余逗号，无更新字段则直接返回
  IF LEN(@sql) <= LEN(N'UPDATE CONTRACT SET ')
    RETURN;
  SET @sql = LEFT(@sql, LEN(@sql) - 1);

  SET @sql += N' WHERE CONTRACT_ID = @contractId';
  EXEC sp_executesql @sql, @params, @contractId = @contractId, @updateData = @updateData;
END
GO

Node.js调用优化 #

直接将前端传入的更新字段转成JSON字符串传入，无需逐个判断：

router.patch('/updateContract', (req, res) => {
  const { contractId, ...updateData } = req.body;
  const request = new sql.Request();

  request.input('contractId', sql.UniqueIdentifier, contractId);
  // 打包更新字段为JSON
  request.input('updateData', sql.NVarChar, JSON.stringify(updateData));

  request.execute('UPDATE_CONTRACT', (err, result) => {
    // 业务处理逻辑
  });
});

前端只需传入expirationDate: null，即可将该字段设为NULL，完全区分「不传入字段」和「传入NULL」的场景。

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