SQL Server молча обрезает varchar в хранимых процедурах

Согласно обсуждению на этом форуме , SQL Server (я используя 2005, но я понимаю, что это также относится к 2000 и 2008) молча обрезает любые varchar, которые вы указываете в качестве параметров хранимой процедуры, до длины varchar, даже если вставка этой строки напрямую с использованием INSERT фактически приведет к ошибке. например. Если я создаю эту таблицу:

CREATE TABLE testTable(
    [testStringField] [nvarchar](5) NOT NULL
)

тогда, когда я выполню следующее:

INSERT INTO testTable(testStringField) VALUES(N'string which is too long')

Я получил ошибку:

String or binary data would be truncated.
The statement has been terminated.

Великий. Целостность данных сохраняется, и вызывающая сторона знает об этом. Теперь давайте определим хранимую процедуру для вставки этого:

CREATE PROCEDURE spTestTableInsert
    @testStringField [nvarchar](5)
AS
    INSERT INTO testTable(testStringField) VALUES(@testStringField)
GO

и выполните его:

EXEC spTestTableInsert @testStringField = N'string which is too long'

Нет ошибок, затронута 1 строка. В таблицу вставляется строка, в которой testStringField как «strin». SQL Server молча обрезал параметр varchar хранимой процедуры.

Теперь, это поведение иногда может быть удобным, но я полагаю, что НЕТ СПОСОБА его отключать. Это очень раздражает, так как я хочу ошибиться, если передать слишком длинную строку хранимой процедуре. Кажется, есть 2 способа справиться с этим.

Во-первых, объявите параметр @testStringField хранимого процесса размером 6 и проверьте, не превышает ли его длина 5. Это выглядит немного взлома и включает в себя раздражающее количество шаблонного кода.

Во-вторых, просто объявите ВСЕ параметры хранимой процедуры varchar как varchar(max), а затем разрешите INSERT в хранимой процедуре не удалось.

Последнее, кажется, работает нормально, поэтому мой вопрос: это хорошая идея - использовать varchar(max) ВСЕГДА для строк в SQL Server хранимые процедуры, если я на самом деле хочу, чтобы хранимый процесс потерпел неудачу при передаче слишком длинной строки? Может ли это быть даже лучшей практикой? Молчание, которое нельзя отключить, кажется мне глупым.

65 голосов | спросил Jez 7 Jpm1000000pmFri, 07 Jan 2011 20:05:55 +030011 2011, 20:05:55

6 ответов


0

Просто есть .

Я никогда не замечал проблемы, потому что одна из моих проверок состояла бы в том, чтобы убедиться, что мои параметры соответствуют длинам столбцов моей таблицы. В коде клиента тоже. Лично я бы ожидал, что SQL никогда не увидит слишком длинные данные. Если бы я увидел усеченные данные, было бы очевидно, что вызвало их.

Если вы чувствуете необходимость использования varchar (max), остерегайтесь огромных проблем с производительностью из-за приоритет типа данных . varchar (max) имеет более высокий приоритет, чем varchar (n) (самый длинный - самый высокий). Таким образом, в этом типе запроса вы будете сканировать, а не искать, и каждое значение varchar (100) от CAST до varchar (max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit:

Существует открыть элемент Microsoft Connect относительно этой проблемы.

И, вероятно, его стоит включить в строгие настройки Эрланда Соммаркога соответствующий элемент подключения ).

Изменить 2 после комментария Мартинса:

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
SELECT @sql = 'B', @nsql = 'B'; 
SELECT 
   LEN(@sql), 
   LEN(@nsql), 
   DATALENGTH(@sql), 
   DATALENGTH(@nsql)
;

DECLARE @t table(c varchar(8000));
INSERT INTO @t values (replicate('A', 7500));

SELECT LEN(c) from @t;
SELECT 
   LEN(@sql + c), 
   LEN(@nsql + c), 
   DATALENGTH(@sql + c), 
   DATALENGTH(@nsql + c) 
FROM @t;
ответил gbn 7 Jpm1000000pmFri, 07 Jan 2011 20:24:31 +030011 2011, 20:24:31
0

Как всегда, спасибо StackOverflow за проведение такого рода углубленного обсуждения. Недавно я просматривал свои хранимые процедуры, чтобы сделать их более устойчивыми, используя стандартный подход к транзакциям и блоки try /catch. Я не согласен с Джо Стефанелли в том, что «мое предложение состоит в том, чтобы сделать приложение ответственным», и полностью согласен с Джезом: «Наличие SQL Server для проверки длины строки будет намного предпочтительнее». Для меня смысл использования хранимых процедур заключается в том, что они написаны на языке, родном для базы данных, и должны выступать в качестве последней линии защиты. На стороне приложения разница между 255 и 256 является просто неизменным числом, но в среде базы данных поле с максимальным размером 255 просто не примет 256 символов. Механизмы проверки приложения должны как можно лучше отражать внутреннюю базу данных, но обслуживание сложно, поэтому я хочу, чтобы база данных давала мне хорошие отзывы, если приложение по ошибке допускает неподходящие данные. Вот почему я использую базу данных вместо набора текстовых файлов с CSV, JSON или чем-то еще.

Я был озадачен, почему один из моих SP выдал ошибку 8152, а другой молча усек. Я наконец-то запутался: у SP, который выдал ошибку 8152, был параметр, который позволял на один символ больше, чем соответствующий столбец таблицы. Столбец таблицы был установлен в nvarchar (255), но параметр был nvarchar (256). Так что, не моя «ошибка» не решает проблему gbn: «проблема с высокой производительностью»? Вместо использования max, возможно, мы могли бы последовательно установить размер столбца таблицы, скажем, 255, а параметр SP - на один символ длиннее, скажем, 256. Это решает проблему тихого усечения и не снижает производительности.  Предположительно, есть еще один недостаток, о котором я не задумывался, но мне он кажется хорошим компромиссом.

Обновление: Боюсь, эта техника не соответствует. Дальнейшее тестирование показывает, что иногда я могу вызвать ошибку 8152, а иногда данные молча усекаются. Буду очень признателен, если кто-нибудь поможет мне найти более надежный способ справиться с этим.

Обновление 2: Пожалуйста, посмотрите ответ Pyitoechito на этой странице.

ответил DavidHyogo 19 MarpmSat, 19 Mar 2011 12:07:48 +03002011-03-19T12:07:48+03:0012 2011, 12:07:48
0

Такое же поведение можно увидеть здесь:

declare @testStringField [nvarchar](5)
set @testStringField = N'string which is too long'
select @testStringField

Мое предложение состоит в том, чтобы на стороне приложения была ответственность за проверку ввода перед вызовом хранимой процедуры.

ответил Joe Stefanelli 7 Jpm1000000pmFri, 07 Jan 2011 20:20:33 +030011 2011, 20:20:33
0
  

Обновление: я боюсь, что эта техника не соответствует. Дальнейшее тестирование показывает, что иногда я могу вызвать ошибку 8152, а иногда данные молча усекаются. Буду очень признателен, если кто-нибудь поможет мне найти более надежный способ справиться с этим.

Это, вероятно, происходит потому, что 256-й символ в строке является пробелом. VARCHAR s обрежет конечный пробел при вставке и просто выдаст предупреждение. Таким образом, ваша хранимая процедура молча усекает ваши строки до 256 символов, а ваша вставка усекает конечный пробел (с предупреждением). Он выдаст ошибку, если указанный символ не будет пробелом.

Возможно, решением было бы сделать VARCHAR хранимой процедуры подходящей длиной для перехвата символа, не являющегося пробелом. VARCHAR(512), вероятно, будет достаточно безопасным.

ответил Jenius 13 J0000006Europe/Moscow 2013, 17:42:32
0

Одним из решений было бы следующее:

  1. Измените все входящие параметры на varchar(max)
  2. Иметь приватную переменную sp с правильной длиной данных (просто скопируйте и вставьте все параметры и добавьте "int" в конце
  3. Объявите переменную таблицы с именами столбцов, совпадающими с именами переменных
  4. Вставьте в таблицу строку, в которой каждая переменная входит в столбец с тем же именем
  5. Выберите из таблицы внутренние переменные

Таким образом, ваши изменения в существующем коде будут очень минимальными, как в примере ниже.

Это оригинальный код:

create procedure spTest
(
    @p1 varchar(2),
    @p2 varchar(3)
)

Это новый код:

create procedure spTest
(
    @p1 varchar(max),
    @p2 varchar(max)
)
declare @p1Int varchar(2), @p2Int varchar(3)
declare @test table (p1 varchar(2), p2 varchar(3)
insert into @test (p1,p2) varlues (@p1, @p2)
select @p1Int=p1, @p2Int=p2 from @test

Обратите внимание, что если длина входящих параметров будет больше предела, вместо того, чтобы молча обрезать строку, SQL Server выдаст ошибку. Сильный>

ответил igorp 19 AMpThu, 19 Apr 2012 05:51:42 +040051Thursday 2012, 05:51:42
0

Вы всегда можете добавить оператор if в ваш sp, который проверяет их длину, и, если они больше указанной длины, выдает ошибку. Это довольно трудоемкий процесс, и обновление будет проблематичным, если вы обновите размер данных.

ответил DForck42 8 Jam1000000amSat, 08 Jan 2011 00:18:59 +030011 2011, 00:18:59

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132