String or binary data will be ignored. The application has been discontinued

Question

I ran into some problems with the SQL server, this is a function I created:

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 request to use this function as follows, it gets an error

String or binary data will be truncated. Statement completed

How to solve it? Thanks you

 select * from testing1(2) 

This is how I create the table

 CREATE TABLE stock(item nvarchar(50) NULL, warehouse nvarchar(50) NULL, price int NULL); 

Answer 1

When you define varchar etc. no length, defaults to 1.

If n is not specified in the declaration definition or variable declaration, the default length is 1. When n is not specified using the CAST function, the default length is 30.

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

Otherwise, you are trying to put> 1 character in nvarchar(1) = fail

As a comment, this is a poor use of the table value function , because it is a "multi statement". It can be written like this and will work 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 can just use the regular SELECT statement ..

Answer 2

The maximum length of the target column is shorter than the value you are trying to insert.

Right-click the table in SQL Manager and go to Design to visualize the table structure and column definitions.

Edit:

Try setting the length in your nvarchar inserts to the same or less than what is indicated in your table.

Answer 3

Specify the size of the item and stock, as in [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 the statement only nvarchar is equal to nvarchar (1), therefore, the column values ​​from the stock table are truncated

Answer 4

In my case, I was getting this error because my table had

 varchar(50) 

but I entered a long string with a length of 67 characters, which led to an error. Change it to

 varchar(255) 

fixed problem.