SQL 字符串或二进制数据将被截断。该语句已终止
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15019397/
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
String or binary data would be truncated. The statement has been terminated
提问by user2098512
I have met some problem with the SQL server, this is the function I created:
我在 SQL 服务器上遇到了一些问题，这是我创建的函数：
ALTER FUNCTION [dbo].[testing1](@price int)
RETURNS @trackingItems1 TABLE (
item nvarchar NULL,
warehouse nvarchar NULL,
price int NULL
)
AS
BEGIN
INSERT INTO @trackingItems1(item, warehouse, price)
SELECT ta.item, ta.warehouse, ta.price
FROM stock ta
WHERE ta.price >= @price;
RETURN;
END;
When I write a query to use that function like the following it getting the error
当我编写查询以使用如下所示的函数时，它收到错误
String or binary data would be truncated. The statement has been terminated
字符串或二进制数据将被截断。该语句已终止
How can I fix this problem?
我该如何解决这个问题？
select * from testing1(2)
This is the way I create the table
这是我创建表的方式
CREATE TABLE stock(item nvarchar(50) NULL,
warehouse nvarchar(50) NULL,
price int NULL);
回答by gbn
When you define varcharetc without a length, the default is 1.
当您定义varchar没有长度的 etc 时，默认值为 1。
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
当数据定义或变量声明语句中未指定 n 时，默认长度为 1。当 CAST 函数中未指定 n 时，默认长度为 30。
So, if you expect 400 bytes in the @trackingItems1column from stock, use nvarchar(400).
因此，如果您希望@trackingItems1来自的列中有400 个字节stock，请使用nvarchar(400).
Otherwise, you are trying to fit >1 character into nvarchar(1)= fail
否则，您正在尝试将 >1 个字符放入nvarchar(1)= 失败
As a comment, this is bad use of table value functiontoo because it is "multi statement". It can be written like this and it will run better
作为评论，这也是表值函数的错误使用，因为它是“多语句”。它可以这样写，它会运行得更好
ALTER FUNCTION [dbo].[testing1](@price int)
RETURNS
AS
SELECT ta.item, ta.warehouse, ta.price
FROM stock ta
WHERE ta.price >= @price;
Of course, you could just use a normal SELECT statement..
当然，您可以只使用普通的 SELECT 语句。
回答by OakNinja
The maximal length of the target column is shorter than the value you try to insert.
目标列的最大长度小于您尝试插入的值。
Rightclick the table in SQL manager and go to 'Design' to visualize your table structure and column definitions.
右键单击 SQL 管理器中的表并转到“设计”以可视化您的表结构和列定义。
Edit:
编辑：
Try to set a length on your nvarchar inserts thats the same or shorter than whats defined in your table.
尝试在 nvarchar 插入上设置一个长度，该长度与表中定义的长度相同或更短。
回答by Hammad Khan
In my case, I was getting this error because my table had
就我而言，我收到此错误是因为我的表有
varchar(50)
but I was injecting 67 character long string, which resulted in thi error. Changing it to
但是我注入了 67 个字符的长字符串，这导致了这个错误。将其更改为
varchar(255)
fixed the problem.
解决了这个问题。
回答by Deb
Specify a size for the itemand warehouselike in the [dbo].[testing1] FUNCTION
像在 [dbo].[testing1] FUNCTION 中指定项目和仓库的大小
@trackingItems1 TABLE (
item nvarchar(25) NULL, -- 25 OR equal size of your item column
warehouse nvarchar(25) NULL, -- same as above
price int NULL
)
Since in MSSQL only saying only nvarcharis equal to nvarchar(1) hence the values of the column from the stocktable are truncated
由于在 MSSQL 中只说nvarchar等于 nvarchar(1) 因此股票表中列的值被截断
