CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Sikandar Sahab
Sikandar Sahab

Reputation: 708

How to solve string or binary data would be truncated in SQL Server stored procedure

The error message:

Msg 8152, Level 16, State 14, Procedure IR_STK_VAL_FIFO, Line 74
String or binary data would be truncated.

The query that I am running and causing the error:

EXEC [dbo].[IR_STK_VAL_FIFO] N'01-Jan-2012', N'25-May-2017', 0, '', 0, '', 0, '', 0, 0, '', N'S', N'05-Jul-2012', 0

I have a stored procedure, in which I am getting error approximately on last line of this table:

CREATE TABLE #PAE_TR_ROWS
(  
     productId varchar(7),
     docType varchar(3),
     docId int,
     docDate SMALLDATETIME,
     qtyIn int,
     qtyOut int,
     rate decimal(12,4),
     sortOrder varchar(255),
     appRate decimal(12,4)
)

Inserting in the above table:

INSERT INTO #PAE_TR_ROWS (productId, docType, docId, docDate, qtyIn, qtyOut, rate, sortOrder, appRate)
VALUES (@new_productId, @new_docType, @new_docId, @new_docDate, 0, @balanceQty, @new_rate, @new_sortOrder, @rate)

Upvotes: 2

Views: 34645

Answers (2)

Lisen Saka
Lisen Saka

Reputation: 56

I was facing the same issue, and the error: String or binary data would be truncated. means that, in your table where you are trying to insert data, one of your columns (type varchar in this case) has the length which in my case I was using the default one and it was 255 chars, so the max_length for this column is less than the chars on the object you are trying to save for this field, so for example:

@Entity
public class Person { 
private String name; 
private String address;
}

The fields defined above will have the default size of chars on the table which is 255 and if the address for one specific person exceeds this max_length, than this error will occur.

Upvotes: 1

Koby Douek
Koby Douek

Reputation: 16675

The error "string or binary data would be truncated" means that you are trying to insert data larger than one of your columns can accept.

This is happening because you have an incorrect order in the values you pass to your EXEC command.

For example, the 1st value you pass is this date:

 EXEC [dbo].[IR_STK_VAL_FIFO] N'01-Jan-2012',N'25-May-2017',0...
                                    ▲

And in the command, it's declared as productId:

                             ▼
INSERT INTO #PAE_TR_ROWS(productId,...
VALUES(@new_productId,...
            ▲

But then, The column productId is only varchar(7), so the value 01-Jan-2012 exceeds that size. That is why you are getting this error.

Upvotes: 5

Related Questions