Thursday, December 6, 2012

How to prevent Stored Procedures from Silently Truncating Data

I just discovered I had a stored procedure that was truncating some data I was updating in a table.  What I wanted the procedure to do is throw an exception, if the string was too long, something like:

Msg 8152, Level 16, State 13, Procedure tb_UpdateData, Line 20
String or binary data would be truncated.


Instead the stored procedure would silently truncate the string without any warning.  What I discovered was that if you increase the parameter value by 1 then it will through an error.

ALTER PROCEDURE [dbo].[tb_UpdateData]

@value nvarchar(51) -- use to be nvarchar(50) adding one character more prevents silent truncating (e.g. it throws an error/exception)

AS
Begin
....
End

StackOverflow - Silent truncating in SQL Sever

No comments: