SQL 解决“字符串或二进制数据将被截断”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15665693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Solving the 'String or binary data would be truncated' error
提问by Art F
Declare @STAF_TO NVARCHAR(100)
I have the following query as part of a SQL Server stored procedure:
我有以下查询作为 SQL Server 存储过程的一部分：
SELECT @SF_TO = TP.FRST_NAME + ' ' + TP.MDLE_NAME + ' ' + TP.LAST_NAME
FROM tbl_Sf TS
INNER JOIN tbl_Pl TP ON (TS.POPL_PK = TP.POPL_PK)
WHERE TS.SF_PK = @ID
I know that @IDis equal to 140 (Type INT) based on a print statement that I inserted right before this. When I try to execute this query separately it works fine.
根据我在此之前插入的打印语句，我知道它@ID等于 140 (Type INT)。当我尝试单独执行此查询时，它工作正常。
However, when I try to run it as part of the stored procedure I get the following error:
但是，当我尝试将它作为存储过程的一部分运行时，出现以下错误：
Msg 8152, Level 16, State 13, Procedure Proc_Name, Line 57
String or binary data would be truncated.
消息 8152，级别 16，状态 13，过程 Proc_Name，第 57 行
字符串或二进制数据将被截断。
Line 57 being where the SELECTstatement is of the query above. Anyone have any ideas why this could be happening?
第 57 行SELECT是上面查询语句所在的行。任何人都知道为什么会发生这种情况？
EDIT: I tried changing NVARCHAR(100)to NVARCHAR(200)and NVARCHAR(MAX), still got the error. Also, the query works fine (with 100 characters) if I run it separately.
编辑：我尝试更改NVARCHAR(100)为NVARCHAR(200)and NVARCHAR(MAX)，仍然出现错误。此外，如果我单独运行该查询，则该查询工作正常（100 个字符）。
回答by Aaron Bertrand
It is almost certain that the definitions of your columns:
几乎可以肯定的是，您的列的定义：
FRST_NAME
MDLE_NAME
LAST_NAME
Add up to more than 98 characters (+2 for the spaces).
添加最多 98 个字符（空格 +2）。
I'd suggest declaring @SF_TOas something bigger than NVARCHAR(100)(but what, exactly, I don't know).
我建议声明@SF_TO为大于NVARCHAR(100)（但究竟是什么，我不知道）。
The error message is annoying to troubleshoot, for sure. It would be great if more specifics were included in the output. Please vote/comment on this Connect item.
当然，错误消息令人讨厌进行故障排除。如果输出中包含更多细节，那就太好了。请对此连接项目投票/评论。
回答by Young Bob
The concatenation statement is almost certainly resulting in records which are longer than the declared length of @SF_TO. You should increase the length of @SF_To.
连接语句几乎肯定会导致记录比@SF_TO 的声明长度更长。您应该增加@SF_To 的长度。
回答by Mike Perrenoud
You're getting this error because the concatenation of:
您收到此错误是因为以下内容的串联：
TP.FRST_NAME + ' ' + TP.MDLE_NAME + ' ' + TP.LAST_NAME
is greater than 100characters. Now, this may not be true on every single row, but it's true on the one you're selecting. If you ran this select statement:
大于100字符。现在，这可能不是每一行都如此，但在您选择的那一行上却是这样。如果您运行此选择语句：
SELECT TP.FRST_NAME + ' ' + TP.MDLE_NAME + ' ' + TP.LAST_NAME
FROM tbl_Sf TS
INNER JOIN tbl_Pl TP ON (TS.POPL_PK = TP.POPL_PK)
WHERE LEN(TP.FRST_NAME + ' ' + TP.MDLE_NAME + ' ' + TP.LAST_NAME) > 100
you would be able to find the offending rows. Another approach is to simply make the @STAF_TOvariable equal to the length of those three columns put together + 2. So, if the columns are all 50characters long in their definitions, @STAF_TOshould be 152.
您将能够找到有问题的行。另一种方法是简单地使@STAF_TO变量等于这三列加在一起的长度 + 2。因此，如果列50在其定义中都是字符长，则@STAF_TO应该是152.
