Ограничить длину символов фиксированным числом в SQL
У меня есть таблица данных под названием «пакет», которая была создана следующим образом:
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
Когда мне нужно обновить значение в таблице данных «пакета» в соответствии с хранимой процедурой, если бы я дал запрос следующим образом, это означает, что значение пакетной ссылки превышает его максимальную длину символов (оно должно иметь длину 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
Поведение переменных/параметров и столбцов, если указано слишком много символов, отличается. Когда вы пытаетесь присвоить значению переменной/параметра слишком длинное значение, значение просто усекается. Например:
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;
Таким образом, проблема здесь в том, что, поскольку вы сначала присваиваете значение переменной/параметру, в этой точке происходит усечение, а затем значение, которое вы INSERT вставляете в таблицу, является действительным; входное значение усекается до 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
...
Проверки поля ввода на стороне клиента недостаточно, особенно если данные могут передаваться через API, через строку запроса, можно манипулировать содержимым публикации и т. д.
SQL Server автоматически обрежет параметр, если вы попытаетесь передать что-то слишком длинное. dbfiddle.uk/DUmDPjAh - вы можете объявить параметр как
varchar(9)и проверить длину 9 внутри процедуры и выдать ошибку