SQL Server 以静默方式截断存储过程中的 varchar

SQL Server silently truncates varchar's in stored procedures

提问by Jez

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 INSERTwould actually cause an error. eg. If I create this table:

根据此论坛讨论，SQL Server（我使用的是 2005，但我认为这也适用于 2000 和 2008）会默默地将varchar您指定为存储过程参数的任何s截断为 varchar 的长度，即使直接使用INSERT实际上会导致错误。例如。如果我创建这个表：

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 testStringFieldas 'strin'. SQL Server silently truncated the stored procedure's varcharparameter.

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

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 wantthe 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 @testStringFieldparameter 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.

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

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

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

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.

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

采纳答案by gbn

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.

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

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)

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

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit:

编辑：

There is an open Microsoft Connect itemregarding this issue.

有一个关于此问题的开放式 Microsoft Connect 项目。

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

它可能值得包含在Erland Sommarkog 的 Strict 设置中（和匹配的 Connect 项）。

Edit 2, after Martins comment:

编辑 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;

回答by DavidHyogo

Thanks, as always, to StackOverflow for eliciting this kind of in-depth discussion. I have recently been scouring through my Stored Procedures to make them more robust using a standard approach to transactions and try/catch blocks. I disagree with Joe Stefanelli that "My suggestion would be to make the application side responsible", and fully agree with Jez: "Having SQL Server verify the string length would be much preferable". The whole point for me of using stored procedures is that they are written in a language native to the database and should act as a last line of defence. On the application side the difference between 255 and 256 is just a meangingless number but within the database environment, a field with a maximum size of 255 will simply not accept 256 characters. The application validation mechanisms should reflect the backend db as best they can, but maintenance is hard so I want the database to give me good feedback if the application mistakenly allows unsuitable data. That's why I'm using a database instead of a bunch of text files with CSV or JSON or whatever.

一如既往，感谢 StackOverflow 引发这种深入的讨论。我最近一直在浏览我的存储过程，以使用标准的事务方法和 try/catch 块使它们更加健壮。我不同意 Joe Stefanelli 的“我的建议是让应用程序端负责”，并且完全同意 Jez：“让 SQL Server 验证字符串长度会更可取”。对我来说，使用存储过程的全部意义在于它们是用数据库本机的语言编写的，应该作为最后一道防线。在应用程序方面，255 和 256 之间的差异只是一个无意义的数字，但在数据库环境中，最大大小为 255 的字段将根本不接受 256 个字符。应用程序验证机制应该尽可能地反映后端数据库，但是维护很困难，所以如果应用程序错误地允许不合适的数据，我希望数据库给我很好的反馈。这就是为什么我使用数据库而不是一堆带有 CSV 或 JSON 或其他格式的文本文件。

I was puzzled why one of my SPs threw the 8152 error and another silently truncated. I finally twigged: The SP which threw the 8152 error had a parameter which allowed one character more than the related table column. The table column was set to nvarchar(255) but the parameter was nvarchar(256). So, wouldn't my "mistake" address gbn's concern: "massive performance issue"? Instead of using max, perhaps we could consistently set the table column size to, say, 255 and the SP parameter to just one character longer, say 256. This solves the silent truncation problem and doesn't incur any performance penalty. Presumably there is some other disadvantage that I haven't thought of, but it seems a good compromise to me.

我很困惑为什么我的一个 SP 抛出了 8152 错误，而另一个却默默地被截断了。我终于明白了：抛出 8152 错误的 SP 有一个参数，该参数允许比相关表列多一个字符。表列设置为 nvarchar(255)，但参数为 nvarchar(256)。那么，我的“错误”不会解决 gbn 的担忧：“大规模性能问题”吗？代替使用 max，也许我们可以始终将表列大小设置为 255，并将 SP 参数设置为仅一个字符长，例如 256。这解决了无提示截断问题并且不会导致任何性能损失。想必还有一些我没有想到的其他缺点，但这对我来说似乎是一个很好的妥协。

Update: I'm afraid this technique is not consistent. Further testing reveals that I can sometimes trigger the 8152 error and sometimes the data is silently truncated. I would be very grateful if someone could help me find a more reliable way of dealing with this.

更新：恐怕这种技术不一致。进一步的测试表明我有时会触发 8152 错误，有时数据会被静默截断。如果有人能帮我找到更可靠的处理方法，我将不胜感激。

Update 2: Please see Pyitoechito's answer on this page.

更新 2：请参阅本页 Pyitoechito 的回答。

回答by Jenius

Update: I'm afraid this technique is not consistent. Further testing reveals that I can sometimes trigger the 8152 error and sometimes the data is silently truncated. I would be very grateful if someone could help me find a more reliable way of dealing with this.

更新：恐怕这种技术不一致。进一步的测试表明我有时会触发 8152 错误，有时数据会被静默截断。如果有人能帮我找到更可靠的处理方法，我将不胜感激。

This is probably occurring because the 256th character in the string is white-space. VARCHARs will truncate trailing white-space on insertion and just generate a warning. So your stored procedure is silently truncating your strings to 256 characters, and your insertion is truncating the trailing white-space (with a warning). It will produce an error when said character is not white-space.

这可能是因为字符串中的第 256 个字符是空格。VARCHARs 将在插入时截断尾随空格并仅生成警告。因此，您的存储过程正在默默地将您的字符串截断为 256 个字符，并且您的插入正在截断尾随的空格（带有警告）。当所述字符不是空格时，它会产生错误。

Perhaps a solution would be to make the stored procedure's VARCHARa suitable length to catch a non-white-space character. VARCHAR(512)would probably be safe enough.

也许一种解决方案是使存储过程VARCHAR具有合适的长度来捕获非空白字符。VARCHAR(512)可能会足够安全。

回答by Joe Stefanelli

The same behavior can be seen here:

在这里可以看到相同的行为：

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

My suggestion would be to make the application side responsible for validating the input before calling the stored procedure.

我的建议是让应用程序端负责在调用存储过程之前验证输入。

回答by igorp

One solution would be to:

一种解决方案是：

1. Change all incoming parameters to be varchar(max)
2. Have sp private variable of the correct datalength (simply copy and paste all in parameters and add "int" at the end
3. Declare a table variable with the column names the same as variable names
4. Insert into the table a row where each variable goes into the column with the same name
5. Select from the table into internal variables

1. 将所有传入参数更改为 varchar(max)
2. 拥有正确数据长度的 sp 私有变量（只需复制并粘贴所有参数并在末尾添加“int”
3. 声明一个表变量，列名与变量名相同
4. 在表中插入一行，其中每个变量进入同名列
5. 从表中选择成内部变量

This way your modifications to the existing code are going to be very minimal like in the sample below.

这样，您对现有代码的修改将非常小，如下面的示例所示。

This is the original code:

这是原始代码：

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

This is the new code:

这是新代码：

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

Note that if the length of the incoming parameters is going to be greater than the limit instead of silently chopping off the string SQL Server will throw off an error.

请注意，如果传入参数的长度将大于限制而不是静默地切断字符串 SQL Server 将抛出错误。

回答by DForck42

You could always throw an if statement into your sp's that check the length of them, and if they're greater than the specified length throw an error. This is rather time consuming though and would be a pain to update if you update the data size.

你总是可以在你的 sp 中抛出一个 if 语句来检查它们的长度，如果它们大于指定的长度，则会抛出一个错误。但是，这相当耗时，并且如果您更新数据大小，则更新起来会很痛苦。

回答by MikeTeeVee

This isn't the Answer that'll solve your problem today, but it includes a Feature Suggestion for MSSQL to consider adding, that would resolve this issue.
It is important to call this out as a shortcoming of MSSQL, so we may help them resolve it by raising awareness of it.
Here's the formal Suggestion if you'd like to vote on it:
https://feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-for

这不是今天可以解决您的问题的答案，但它包含了 MSSQL 考虑添加的功能建议，可以解决此问题。
将其称为 MSSQL 的缺点很重要，因此我们可以通过提高对它的认识来帮助他们解决它。
如果您想对其进行投票，这里是正式建议：https:
//feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-为了

I share your frustration.
The whole point of setting Character-Size on Parameters is so other Developers will instantly know
    what the Size Limits are (via Intellisense) when passing in Data.
This is like having your documentation baked right into the Sproc's Signature.

我分担你的沮丧。
在参数上设置字符大小的全部目的是让其他开发人员
    在传入数据时立即知道大小限制是什么（通过智能感知）。
这就像将您的文档直接放入 Sproc 的签名中。

Look, I get it, Implicit-Conversionduring Variable Assignments is the culprit.
Still, there is no good reason to expend this amount of energy battling scenarios
    where you are forced to work around this feature.
If you ask me, Sprocs and Functions should have the same engine-rules in place,
    for Assigning Parameters, that are used when Populating Tables. Is this reallytoo much to ask?

看，我明白了，变量赋值期间的隐式转换是罪魁祸首。
尽管如此，
    在您被迫解决此功能的情况下，没有充分的理由花费如此大量的精力来对抗场景。
如果你问我，Sprocs 和 Functions 应该具有相同的引擎规则，
    用于分配参数，在填充表时使用。这真的是问太多了吗？

All these suggestions to use Larger Character-Limits
    and thenadding Validation for EACH Parameter in EVERY Sproc is ridiculous.
I know it's the only way to ensure Truncation is avoided, but really MSSQL?
I don't care if it's ANSI/ISO Standard or whatever, it's dumb!

所有这些建议使用更大的字符的限制
    ，并随后将验证每个参数的存储过程EVERY是荒谬的。
我知道这是确保避免截断的唯一方法，但真的是 MSSQL？
我不在乎它是 ANSI/ISO 标准还是其他什么，它是愚蠢的！

When Values are too long - I want my code to break - every time.
It should be: Do not pass go, and fix your code.
You could have multiple truncation bugs festering for years and never catch them.
What happened to ensuring your Data-Integrity?

当值太长时 - 我希望我的代码每次都被破坏。
应该是：不要通过 go，并修复您的代码。
您可能有多个截断错误多年，但永远不会发现它们。
确保您的数据完整性发生了什么？

It's dangerous to assume your SQL Code will only ever be called afterall Parameters are Validated.
I try to add the same Validation to both my Website and in the Sproc it calls,
    and I stillcatch Errors in my Sproc that slipped past the website. It's a great sanity-check!
What if you want to re-use your Sproc for a WebSite/WebService and also have it called from other
    Sprocs/Jobs/Deployment/Ad-Hoc Scripts (where there is nofront-end to Validate Parameters)?

假设您的 SQL 代码只会在所有参数都经过验证后才会被调用，这是很危险的。
我尝试将相同的验证添加到我的网站和它调用的 Sproc 中，但
    我仍然在我的 Sproc 中发现了滑过网站的错误。这是一个很好的健全性检查！
如果您想将您的 Sproc 重新用于网站/WebService 并且还从其他
    Sprocs/Jobs/Deployment/Ad-Hoc 脚本（没有用于验证参数的前端）调用它，该怎么办？

MSSQL Needs a "NO_TRUNC" Option to Enforce this on anyNon-Max String Variable
    (even those used as Parameters for Sprocs and Functions).
It could be Connection/Session-Scoped:
    (like how the "TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" Option affects all Queries)
Or focused on a Single Variable:
    (like how "NOLOCK" is a Table Hint for just 1 Table).
Or a Trace-Flag or Database Property you turn on to apply this to All Sproc/Function Parameters in the Database.

MSSQL 需要一个“ NO_TRUNC”选项来对任何非最大字符串变量
    （甚至那些用作 Sprocs 和函数的参数）强制执行此操作。
它可能是连接/会话范围的：（
    例如“ TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”选项如何影响所有查询）
或专注于单个变量：（
    例如“ NOLOCK”是仅 1 个表的表提示）。
或者您打开的跟踪标志或数据库属性将其应用于数据库中的所有 Sproc/函数参数。

I'm not asking to upend decades of Legacy Code.
Just asking MS for the option to better manage our Databases.

我不是要颠覆几十年的遗留代码。
只是要求 MS 提供更好地管理我们数据库的选项。