SQL中如何触发字符长度超限异常？（batch表更新场景）

问题背景 #

现有batch数据表及对应更新存储过程，当传入的batch_ref值超过其定义的最大长度8时，无论通过存储过程还是直接执行UPDATE语句，都希望触发字符长度超限异常，但修改字段数据类型（char(8)、nchar(8)、nvarchar(8)）均未达到预期效果。

数据表创建语句 #

CREATE TABLE [dbo].[batch](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [batch_ref] [varchar](8) NOT NULL,
    [data_number_of_rows] [int] NOT NULL,
 CONSTRAINT [pk_batch] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [uc_batch_ref] UNIQUE NONCLUSTERED 
(
    [batch_ref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

更新存储过程定义 #

CREATE PROCEDURE [dbo].[update_batch_data_number_of_rows]
    @batch_ref varchar(8),
    @data_number_of_rows int
AS
BEGIN
    UPDATE [dbo].[batch]
    SET [data_number_of_rows] = @data_number_of_rows
    WHERE batch_ref = @batch_ref;

    SELECT @@ROWCOUNT AS Updated;
END
GO

测试场景（传入超长batch_ref） #

BEGIN
    UPDATE [dbo].[batch]
    SET [data_number_of_rows] = '55'
    WHERE batch_ref = '12345678910';

    SELECT @@ROWCOUNT AS Updated;
END
GO

解决方案 #

1. 存储过程中添加参数长度校验 #

SQL Server默认仅在插入/更新字段值时，若开启ANSI_WARNINGS会触发截断报错；但WHERE子句中的参数截断只会产生警告，不会中断执行。因此需在存储过程中手动校验参数长度，触发自定义异常：

修改后的存储过程：

CREATE PROCEDURE [dbo].[update_batch_data_number_of_rows]
    @batch_ref varchar(8),
    @data_number_of_rows int
AS
BEGIN
    -- 校验batch_ref长度，超过8则抛出异常
    IF LEN(@batch_ref) > 8
    BEGIN
        THROW 50001, 'batch_ref长度不能超过8个字符', 1;
    END

    UPDATE [dbo].[batch]
    SET [data_number_of_rows] = @data_number_of_rows
    WHERE batch_ref = @batch_ref;

    SELECT @@ROWCOUNT AS Updated;
END
GO

注：若需区分字节长度（如多字节字符），可改用DATALENGTH(@batch_ref) > 8进行校验。

2. 直接执行UPDATE时的手动校验 #

对于直接执行UPDATE的场景，需先校验传入值的长度，再执行更新逻辑：

DECLARE @input_batch_ref varchar(50) = '12345678910';

-- 校验长度
IF LEN(@input_batch_ref) > 8
BEGIN
    THROW 50001, 'batch_ref长度不能超过8个字符', 1;
END
ELSE
BEGIN
    UPDATE [dbo].[batch]
    SET [data_number_of_rows] = 55
    WHERE batch_ref = @input_batch_ref;

    SELECT @@ROWCOUNT AS Updated;
END
GO

3. 确保ANSI_WARNINGS配置开启 #

确认数据库ANSI_WARNINGS设置为ON（默认开启），该配置会在字段值被截断时触发原生报错，避免静默截断：

SET ANSI_WARNINGS ON;

关键说明 #

修改字段数据类型无效的原因：无论char/nchar/nvarchar，SQL Server对WHERE子句中的超长参数只会自动截断并匹配，不会触发异常；仅当尝试将超长值写入字段时，ANSI_WARNINGS ON才会报错。因此必须通过手动参数校验实现WHERE子句的超长值拦截。

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