"SqlException: String or binary data would be truncated." - Is there an EASY way to debug this?

Question

When executing a stored procedure that produces this error, it may say the error is on around line 42. But the error is nowhere near 42. Sometimes it's in a join, sometimes it's in a function, sometimes a subquery... but line 42 is never anywhere near or even remotely relevant to the error.

What I typically have to do is copy all the stored procedure code, recreate all the inputs and set default values, then start breaking it down and re-executing it piece by piece to find which bit of data is being truncated. When the error disappears, I know something in the last batch of code I stripped out was causing it, so I start breaking that batch down. It's a pain, and takes a reasonably long time to do for such a simple error with such a simple fix.

Is there an easier way to debug this that I might be missing?

Answer 1

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary. - see the example below.

Code Snippet

--This batch will fail with the SQL Server error message

 DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

 DECLARE @MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @MyParameter = REPLICATE('Z', 52)

INSERT INTO @MyTable(MyValue)

VALUES (@MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @MyParameter = REPLICATE('Z', 52)

IF LEN(@MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

 INSERT INTO @MyTable(MyValue)

 VALUES (@MyParameter)

END

GO