SQL Server 以静默方式截断存储过程中的 varchar [英] SQL Server silently truncates varchar's in stored procedures

问题描述

根据本论坛讨论，SQL Server(我是使用 2005，但我认为这也适用于 2000 和 2008)将您指定为存储过程参数的任何 varchar 静默地截断为 varchar 的长度，即使直接使用 INSERT 插入该字符串 实际上会导致错误.例如.如果我创建这个表:

创建表 testTable([testStringField] [nvarchar](5) 非空)

然后当我执行以下操作时:

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

我收到一个错误:

字符串或二进制数据将被截断.该语句已终止.

太好了.数据完整性得到保护，调用者知道这一点.现在让我们定义一个存储过程来插入:

创建程序 spTestTableInsert@testStringField [nvarchar](5)作为插入 testTable(testStringField) VALUES(@testStringField)去

并执行它:

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

没有错误，1 行受到影响.将一行插入到表中，testStringField 为strin".SQL Server 以静默方式截断了存储过程的 varchar 参数.

现在，这种行为有时可能很方便，但我认为没有办法将其关闭.这非常烦人，因为如果我将太长的字符串传递给存储过程，我希望出错.似乎有两种方法可以解决这个问题.

首先，将存储过程的 @testStringField 参数声明为大小 6，并检查其长度是否超过 5.这看起来有点像 hack，涉及大量的样板代码.>

其次，只需将所有存储过程varchar参数声明为varchar(max)，然后让存储过程中的INSERT语句失败.

后者似乎工作正常，所以我的问题是:如果我真的希望存储过程在 SQL Server 存储过程中使用 varchar(max) 总是一个好主意传递太长的字符串时失败?它甚至可以成为最佳实践吗?无法禁用的静音截断对我来说似乎很愚蠢.

解决方案

It just is.

我从来没有注意到问题，因为我的一项检查是确保我的参数与我的表列长度匹配.在客户端代码中也是.就个人而言，我希望 SQL 永远不会看到太长的数据.如果我确实看到了截断的数据，那么很明显是什么导致了它.

如果您确实觉得需要 varchar(max)，请注意一个巨大的性能问题，因为 数据类型优先级.varchar(max) 的优先级高于 varchar(n)(最长的最高).因此，在这种类型的查询中，您将获得扫描而不是搜索，并且每个 varchar(100) 值都被 CAST 转换为 varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

有一个 打开 Microsoft Connect 项目关于此问题.

它可能值得包含在 Erland Sommarkog 的严格设置(和 匹配连接项).

编辑 2，在 Martins 评论后:

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);SELECT @sql = 'B', @nsql = 'B';选择LEN(@sql),LEN(@nsql),数据长度(@sql),数据长度(@nsql);声明@t table(c varchar(8000));INSERT INTO @t 值(复制('A'，7500))；从@t 中选择 LEN(c)；选择LEN(@sql + c),LEN(@nsql + c),数据长度(@sql + c),数据长度(@nsql + c)从T;

According to this forum discussion, SQL Server (I'm using 2005 but I gather this also applies to 2000 and 2008) silently truncates any varchars you specify as stored procedure parameters to the length of the varchar, even if inserting that string directly using an INSERT would actually cause an error. eg. If I create this table:

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

then when I execute the following:

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

I get an error:

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

Great. Data integrity preserved, and the caller knows about it. Now let's define a stored procedure to insert that:

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

and execute it:

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

No errors, 1 row affected. A row is inserted into the table, with testStringField as 'strin'. SQL Server silently truncated the stored procedure's varchar parameter.

Now, this behaviour might be convenient at times but I gather there is NO WAY to turn it off. This is extremely annoying, as I want the thing to error if I pass too long a string to the stored procedure. There seem to be 2 ways to deal with this.

First, declare the stored proc's @testStringField parameter as size 6, and check whether its length is over 5. This seems like a bit of a hack and involves irritating amounts of boilerplate code.

Second, just declare ALL stored procedure varchar parameters to be varchar(max), and then let the INSERT statement within the stored procedure fail.

The latter seems to work fine, so my question is: is it a good idea to use varchar(max) ALWAYS for strings in SQL Server stored procedures, if I actually want the stored proc to fail when too long a string is passed? Could it even be best practice? The silent truncation that can't be disabled seems stupid to me.

解决方案

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit:

There is an open Microsoft Connect item regarding this issue.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings (and matching Connect item).

Edit 2, after Martins comment:

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;

这篇关于SQL Server 以静默方式截断存储过程中的 varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！