代码之家 › 专栏 › 技术社区 › Punya Munasinghe

在SQL中将字符长度限制为固定数字

sql-query-store sql-server sql

Punya Munasinghe · 技术社区 · 2 年前

我有一个名为“批处理”的数据表,其创建过程如下:;

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]

此外,我在“批处理”表上编写了一个名为“update_batch_data_number_of_rows”的存储过程,如下所示;

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值超过了它的最大字符长度(它应该有8个长度,但尝试使用10个长度),我需要有一个超过最大字符长度的SQL异常。我尝试将数据类型从varchar(8)更改为char(8)、nchar(8)和nvarchar(8),但没有收到预期的答案。我正在寻找一种可能的方法来做到这一点。有人能帮我建立这个逻辑吗?

BEGIN
        UPDATE [dbo].[batch]
        SET [data_number_of_rows] = '55'
        WHERE batch_ref = '12345678910';
    
        SELECT @@ROWCOUNT AS Updated;
    END
    GO

1 回复 | 直到 2 年前

Thom A 2 年前

当提供太多字符时,变量/参数和列的行为会有所不同。当您尝试为变量/参数的值分配过长的值时,该值将被截断。例如:

DECLARE @MyString varchar(2) = 'abc';
SELECT @MyString;

@MyString 被赋予该值 'ab' ; 这个 'c' 丢失。

对于列,当您尝试 INSERT 或 UPDATE 一行并提供一个对于列来说太长的值,您会收到一个错误,该值将被截断,语句失败:

CREATE TABLE dbo.MyTable (MyString varchar(2));
GO
INSERT INTO dbo.MyTable (MyString)
VALUES('abc');
GO
DROP TABLE dbo.MyTable;

因此,这里的问题是,当您首先将值分配给变量/参数时,截断发生在该点,然后将值 插入 进入表格是有效的;输入值被截断为 varchar(8) 所以这很合适。

老实说,真正解决这个问题的地方是在应用层;文本字段不应允许超过8个字符,并且不能传递超过8个的字符。但是,如果您也希望在SQL层中处理它,请将参数定义为一个字符 太久了 对于您的列,如果值为9+个字符,则会发生截断错误:

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

    SELECT @@ROWCOUNT AS Updated; --This should probably be an OUTPUT parameter?
END;
GO

如果你愿意,你可以检查参数是否太长,并抛出自己的错误,在旧版本的SQL Server上,截断错误可能会有点模糊:


IF LEN(@batch_ref) > 8
    THROW 50001, N'The value of the parameter @data_number_of_rows cannot be greater than 8 characters long.', 10;
ELSE BEGIN
    ...