SQLServer 在存储过程中静默地截断 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作为“ string”。SQLServer 悄悄地截断了存储过程的 varchar参数。

现在，这种行为有时可能很方便，但我想没有办法把它关掉。这是非常恼人的，因为如果我向存储过程传递了太长的字符串，想要就会出错。有两种方法可以解决这个问题。

首先，将存储进程的 @testStringField参数声明为大小6，并检查其长度是否超过5。这看起来有点像黑客行为，而且涉及到大量令人恼火的样板代码。

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

后者似乎工作得很好，所以我的问题是: 如果我实际上希望存储过程在传递过长的字符串时失败，那么在 SQL Server 存储过程中对字符串使用 varchar(max) ALWAYS 是一个好主意吗？这会是最好的练习吗？无法禁用的无声截断在我看来很愚蠢。

47944 次浏览
小开

同样的行为可以在这里看到:

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

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

小开
最佳答案

就是 是。

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

如果您确实感到需要 varchar (max) ，请注意由于 数据类型优先级而产生的大量性能问题。Varchar (max)的优先级高于 varchar (n)(最长的是最高的)。所以在这种类型的查询中，您将得到一个扫描而不是一个搜索，并且每个 varchar (100)值都是 CAST 到 varchar (max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

编辑:

关于这个问题有一个 打开 MicrosoftConnect 项。

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

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

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

小开

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

我很困惑为什么我的一个 SP 会抛出8152错误，而另一个却默默地被截断了。我最后修改了一下: 抛出8152错误的 SP 有一个参数，它允许比相关的表列多一个字符。Table 列被设置为 nvarchar (255) ，但参数是 nvarchar (256)。那么，难道我的“错误”不能解决 gbn 的担忧: “巨大的性能问题”吗？与使用 max 相反，也许我们可以始终如一地将表列大小设置为，比如255，将 SP 参数设置为更长的一个字符，比如256。这解决了静默截断问题，并且不会带来任何性能损失。 可能还有一些我没有想到的缺点，但对我来说，这似乎是一个很好的折衷方案。

更新: 恐怕这种方法不一致。进一步的测试表明，我有时可以触发8152错误，有时数据被静默截断。如果有人能帮我找到一个更可靠的方法来解决这个问题，我将不胜感激。

更新2: 请在本页看到 Pyitoechito 的答案。

小开

一个解决办法是:

  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

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

小开

更新: 恐怕这种技术是不一致的。进一步的测试表明，我有时可以触发8152错误，有时数据被静默截断。如果有人能帮我找到一个更可靠的方法来解决这个问题，我将不胜感激。

这可能是因为字符串中的第256个字符是空格。VARCHAR将截断插入时的尾部空白，只生成一个警告。因此，您的存储过程将静默地将字符串截断为256个字符，并且您的插入将截断尾随的空白(带有警告)。当所述字符不是空白时，它将产生一个错误。

也许一个解决方案是使存储过程的 VARCHAR成为一个合适的长度，以捕获非空白字符。VARCHAR(512)可能足够安全。

小开

这不是今天可以解决问题的答案，但是它包含了一个 MSSQL 可以考虑添加的特性建议，可以解决这个问题。
指出这是 MSSQL 的一个缺点是很重要的，因此我们可以通过提高对它的认识来帮助他们解决这个问题。
如果你想投票表决的话，这里有一个正式的建议:
Https://feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-for

我理解你的沮丧。
设置字符大小参数的重点是让其他开发人员立即知道
传入数据时的大小限制是什么(通过 Intellisense)。
这就像将您的文档直接添加到 Sproc 的签名中一样。

听着，我明白了，变量作业中的 隐式转换是罪魁祸首。
尽管如此，还是没有充分的理由花费这么多的能源战斗场景
在这里，您必须围绕这个特性工作。
如果你问我，Sprocs 和函数应该有相同的引擎规则,
用于指定填充表时使用的参数。这个 真的是否过分？

所有这些建议使用较大的字符限制
而 那么在每个 Sproc 中添加每个参数的验证是荒谬的。
我知道这是避免截断的唯一方法，但是真的是 MSSQL 吗？
我不在乎它是否是 ANSI/ISO 标准或其他什么，它是愚蠢的！

当值太长时-我希望我的代码打破-每次。
它应该是: < em > Do not pass go，并修复您的代码。
您可能有多个截断错误溃烂多年，从来没有赶上他们。
不是说要确保数据完整性吗？

假设您的 SQL 代码将永远只被称为 < em > 在 之后是危险的，所有参数都已验证。
我试图添加相同的验证到我的网站和 Sproc 调用,
我的 还是捕捉错误在我的 Sproc 滑过网站。这是一个伟大的理智检查！
如果您希望将 Sproc 重用于 WebSite/WebService，并从其他
Sprocs/Jobs/Deployment/Ad-Hoc Scripts (在哪里有验证参数的 没有前端) ？

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

我不是要求颠覆几十年的遗产代码。
只是要求微软的选择，以更好地管理我们的数据库。


提交答案