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

Question

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); 

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