Restrict the character length to a fixed number in SQL

Question

I have a data table called 'batch' which has been created as follows;

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]

Also, I have a stored procedure called 'update_batch_data_number_of_rows' written on 'batch' table as follows;

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

When I need to update a value in 'batch' data table according to the stored procedure if I would given the query as follows, that means batch_ref value exceeding the maximum character length of it (it should have 8 length but trying with 10 length), I need to have a SQL exception for exceeding maximum character length. I tried with changing data types from varchar(8) to char(8), nchar(8) and nvarchar(8) but expected answer wasn't received. I'm looking for a possible way of doing this. Can anyone help me to build up this logic?

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

Answer 1

The behaviour of variables/parameters and columns when provided too many characters differs. When you attempt to assign the value of a variable/parameter a value that is too long, the value is simply truncated. For example:

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

@MyString is assigned the value 'ab'; the 'c' is lost.

For columns, when you try to INSERT or UPDATE a row and provide a value too long for a column you instead receive an error that the value would be truncated, and the statement fails:

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

The problem here, therefore, is that as you are assigning the value to a variable/parameter first where truncation occurs at that point, and then the value you INSERT into the table is valid; the input value is truncated to a varchar(8) and so that fits.

Honestly, the place to actually address this is in your application layer; the text field shouldn't allow more than 8 characters, and then you can't pass more than 8. If, however, you want to handle it in the SQL layer as well, then define your parameter as one character too long for your column, as then if the value is 9+ characters, a truncation error will occur:

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

If you prefer, you can check if the parameter is too long, and throw your own error, and the truncate errors can be a little ambiguous on older versions of 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
    ...