SQL Server在存储过程中默默地截断varchar [英] SQL Server silently truncates varchar's in stored procedures

问题描述

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

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')

我得到一个错误:

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

并执行它:

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

没有错误，影响了1行.在表中插入一行，其中testStringField为'strin'. SQL Server默默地截断了存储过程的varchar参数.

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.

首先，将存储的proc的@testStringField参数声明为大小6，并检查其长度是否超过5.这似乎有点不合常规，并且涉及到令人讨厌的样板代码.

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.

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

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

后者似乎很好用，所以我的问题是:如果我真的希望在传递字符串的时间过长时存储过程失败，那么对SQL Server存储过程中的字符串始终使用varchar(max)是否是一个好主意? ?可能是最佳做法吗?在我看来，无法禁用的无声截断是愚蠢的.

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.

推荐答案

是.

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

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

有一个它可能值得包含在 Erland Sommarkog的严格设置(和

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

在马丁斯发表评论后

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屋！