Elusive “String or binary data would be truncated” error

This was driving me nuts. I have a very basic SQL code similar to

ALTER PROCEDURE Proc1(@val1 NVARCHAR(max))
AS
BEGIN
    -- some code
    EXEC Proc2 @val2 = @val1
    -- some other code
END

I was getting error “String or binary data would be truncated”, but only when 2 conditions were met:

  1. @val1 is quite large
  2. Either SQL Server/computer just restarted or stored procedure Proc2 was just updated (ALTER)

Error happened just by the fact of the EXEC Proc2 being there it didn’t even had to do anything, it could RETURN straight away. Both @val2 an @val1 are of a type NVARCHAR(max) so there is no reason for the error. The error happened only once, after that I can pass data of any size – and no error would happen. Like I said – nuts.

Maybe it’s a bug in SQL Server – it doesn’t expect such a large value from start, maybe I am doing something wrong. The solution I came up with is kinda weird, but it works: On the very fist call to Proc1 I pass a very small predefined value for @val1 parameter, something like !TEST!. The call goes without any error and and all consecutive calls with any data size as well. The only thing Proc2 has to detect that !TEST! value is passed and don’t do any actual work with it, just RETURN straight back

Leave a Reply

Your email address will not be published. Required fields are marked *