字符串或二进制数据将被截断。语句已终止

我在 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;

当我编写一个查询来使用该函数时，如下所示，它会得到错误

字符串或二进制数据将被截断。语句已终止

我怎样才能解决这个问题？

select * from testing1(2)

这是我创建表的方法

CREATE TABLE stock(item       nvarchar(50) NULL,
warehouse   nvarchar(50) NULL,
price int NULL);
448001 次浏览
小开

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.

Edit:

Try to set a length on your nvarchar inserts thats the same or shorter than whats defined in your table.

小开

When you define varchar etc without a length, the default is 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.

So, if you expect 400 bytes in the @trackingItems1 column from stock, use nvarchar(400).

Otherwise, you are trying to fit >1 character into nvarchar(1) = fail

As a comment, this is bad use of table value function too 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..

小开

Specify a size for the item and warehouse like in the [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 nvarchar is equal to nvarchar(1) hence the values of the column from the stock table are truncated

小开

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 

varchar(255)

fixed the problem.

小开

SQL Server 2016 SP2 CU6 and SQL Server 2017 CU12 introduced trace flag 460 in order to return the details of truncation warnings. You can enable it at the query level or at the server level.

Query level

INSERT INTO dbo.TEST (ColumnTest)
VALUES (‘Test truncation warnings’)
OPTION (QUERYTRACEON 460);
GO

Server Level

DBCC TRACEON(460, -1);
GO

From SQL Server 2019 you can enable it at database level:

ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = ON;

The old output message is:

Msg 8152, Level 16, State 30, Line 13
String or binary data would be truncated.
The statement has been terminated.

The new output message is:

Msg 2628, Level 16, State 1, Line 30
String or binary data would be truncated in table 'DbTest.dbo.TEST', column 'ColumnTest'. Truncated value: ‘Test truncation warnings‘'.

In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.


提交答案