String or binary data would be truncated. The statement has been terminated

I have met some problem with the SQL server, this is the 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 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);

Solution 1:

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..

Solution 2:

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.

Solution 3:

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.

Solution 4:

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