Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 439
Nick Chammas Avatar asked Jan 23 '12 21:01

Nick Chammas


People also ask

How do I ignore truncation error in SQL Server?

To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.

What is truncate error in SQL?

The "String or binary data would be truncated" error indicates that the procedure is attempting to store something in the DBServerInfo table that is larger than the column allows. The two known reasons this can occur are: SQL Server has at least one database whose name exceeds 25 characters in length.


1 Answers

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).

like image 69
Remus Rusanu Avatar answered Nov 06 '22 07:11

Remus Rusanu