How do I keep my stored procedure inputs from being silently truncated?

Question

We use the standard System.Data classes, DbConnection and DbCommand, to connect to SQL Server from C#, and we have many stored procedures that take VARCHAR or NVARCHAR parameters as input. We found that neither SQL Server nor our C# application throws any kind of error or warning when a string longer than maximum length of a parameter is passed in as the value to that parameter. Instead, the value is silently truncated to the maximum length of the parameter.

So, for example, if the stored procedure input is of type VARCHAR(10) and we pass in 'U R PRETTY STUPID', the stored procedure receives the input as 'U R PRETTY', which is very nice but totally not what we meant to say.

What I've done in the past to detect these truncations, and what others have likewise suggested, is to make the parameter input length one character larger than required, and then check if the length of the input is equal to that new max length. So in the above example my input would become VARCHAR(11) and I would check for input of length 11. Any input of length 11 or more would be caught by this check. This works, but feels wrong. Ideally, the data access layer would detect these problems automatically.

Is there a better way to detect that the provided stored procedure input is longer than allowed? Shouldn't DbCommand already be aware of the input length limits?

Also, as a matter of curiosity, what is responsible for silently truncating our inputs?

Answer 1

Use VARCHAR(8000), NVARCHAR(4000) or even N/VARCHAR(MAX), for all the variables and parameters. This way you do not need to worry about truncation when assigning @variables and @parameters. Truncation may occur at actual data write (insert or update) but that is not silent, is going to trigger a hard error and you'll find out about it. You also get the added benefit of the stored procedure code not having to be changed with schema changes (change column length, code is still valid). And you also get better plan cache behavior from using consistent parameter lengths, see How Data Access Code Affects Database Performance.

Be aware that there is a slight performance hit for using MAX types for @variables/@parameters, see Performance comparison of varchar(max) vs. varchar(N).