В SQL Server в автоматическом режиме усекает тип varchar в хранимые процедуры

по данным форуме, SQL Server (я использую 2005, но я понимаю, что это также относится к 2000 и 2008) молча усекает любой varchars вы указываете в качестве параметров хранимой процедуры длину 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 в автоматическом режиме усе хранимой процедуры .



теперь, это поведение может быть удобно время от времени, но я понимаю, что нет никакого способа, чтобы повернуть его выключено. Это очень раздражает, как я!--28-->хочу дело в ошибке, если я передаю слишком длинную строку в хранимую процедуру. Есть 2 способа бороться с этим.



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



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



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

644   6  

6 ответов:

Это просто - это.

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

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

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit:

есть открыть элемент Microsoft Connect по этому вопросу.

и это, вероятно, достойно включения в строгие настройки Эрланда Соммаркога (и соответствующий пункт 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;
28  
2012-01-24 00:41:11

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

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

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

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

14  
2013-06-14 12:51:28

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

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

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

4  
2011-01-07 19:20:33

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

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

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

4  
2013-06-13 16:42:32

одним из решений может быть:

  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 выдаст ошибку.

1  
2012-04-19 09:30:44

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

0  
2012-04-19 11:48:19

Comments

    Ничего не найдено.
Click here to cancel reply.