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

sqlsql server

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

Best Answer

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

Related Solutions

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

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

You do not need to have quotes around any of your paramters.

change your sql to:

update dbo.JobStatus SET ShipTrackingNumber = @trackingNumber, 
ShipMethodTransmitted = @TransmitMethod, 
DateShipTransmitProcessed = @DateProcessed, 
ShipmentProcessedBy = @ProcessedBy, Critical = @Critical, 
ShipTransmitStatus = @TransmitStatus WHERE JobTableId = @JobTableId

It is basically trying to insert the string "@Critical" into a nchar(1) which is causing the truncation error.

Best Answer

Related Solutions

SQLite – UPSERT *not* INSERT or REPLACE

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

Related Topic

SQLite – UPSERT not INSERT or REPLACE