CODEWITHSUNDEEP
sqlsql-servert-sql
Tony_Henrich
Tony_Henrich

Reputation: 44075

"String or binary data would be truncated" error when value is same size as column size

I have a customer table in SQL Server 2012 with 'status' column is of type varchar(1).

This statement is giving a 'String or binary data would be truncated' error

exec sp_executesql N'update customer set status=''@stat'' 
where custID=@custID',N'@stat varchar(1),@custID bigint',@stat='B',@custID=940

I don't know why because I am passing 'B' as a single character. I get the same error if @stat=''

However this statement runs fine:

 exec sp_executesql N'update customer set status=''B'' 
where custid=@custID',N'@custID bigint',@custID=940

It seems there's a subtle issue I am missing. Any ideas?

Upvotes: 0

Views: 768

Answers (1)

Matt Gibson
Matt Gibson

Reputation: 38238

You're mistakenly trying to set status to the literal string '@stat', not the value of the variable @stat. The string '@stat' has five characters, and would be truncated when inserting into a VARCHAR(1). Try:

exec sp_executesql N'update customer set status=@stat 
where custID=@custID',N'@stat varchar(1),@custID bigint',@stat='B',@custID=940

Upvotes: 6

Related Questions