Inserting data in SQL Server table not showing error on both condition

Question

I have use procedure to insert data into table for fixed column size. But data trim and inserted successfully without any error.

I have lost some content from that variable.

This code snippet is not showing any error:

declare @temp varchar(5)
declare @tm table (a varchar(5))
set @temp ='abcdefghijkl'

insert into @tm 
values(@temp)

select * from @tm

But this code snippet is showing this error:

String or binary data would be truncated

declare @temp1 varchar(5)
declare @tm1 table (a varchar(5))

insert into @tm1 
values('abcdefghijkl')

select * from @tm1

Zohar Peled · Accepted Answer · 2019-07-18 07:32:34Z

1

The fact that the second code snippet is raising an error is a good thing.
It prevents you from corrupting data by mistake.

In the first code snippet, however, SQL Server will silently trim the string due to implicit conversion rules.
Whenever you attempt to populate a variable with a data that has a different data type, SQL Server will attempt to implicitly convert the data type to the data type of the variable.

This is well documented in the Converting Character Data section of the char and varchar (Transact-SQL) page:

When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated.

This does not happen when inserting into a table, providing ANSI_WARNINGS is set to ON (which is the default state).
When ANSI_WARNINGS is set to ON, you get the

String or binary data would be truncated

error message.
When it's set to OFF, however, the implicit conversion will silently truncate the data:

set ansi_warnings off;

declare @temp1 varchar(5)
declare @tm1 table (a varchar(5))

insert into @tm1 
values('abcdefghijkl')

select * from @tm1

Result:

a
abcde

Note: The ansi_warnings state does not have effect the implicit conversion when setting a variable value - it will always be truncated regardless of the ansi_warnings state.

edited Jul 18, 2019 at 7:32

answered Jul 18, 2019 at 6:25

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Saket Yadav Over a year ago

It's working but for first snippet when use variable to insert. It's not showing warning. Data has been trim as per table column size.

Zohar Peled Over a year ago

Yes, I know. I've tried to explain why this is happening in my answer.

Saket Yadav Over a year ago

But any solution to use in procedure to get this warning and data not trim to store into table.

Zohar Peled Over a year ago

You can test the length of the string before assigning it to the variable - if len(<string here>) > <variable length here> raise error.

Collectives™ on Stack Overflow

Inserting data in SQL Server table not showing error on both condition

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related