We use standard classes System.Data, DbConnectionand DbCommandto connect to SQL Server with C #, and we have many stored procedures that accept parameters VARCHARor NVARCHARas input. We found that neither SQL Server nor our C # application caused any errors or warnings when a string whose length exceeds the maximum length of a parameter is passed as the value of this parameter. Instead, the value is silently truncated to the maximum length of the parameter.
So, for example, if the input of the stored procedure 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 completely not what we wanted to say.
What I did in the past to detect these truncations, and what others also suggested , is to make the input parameter one character longer than required, and then check if the input length is equal to this new maximum length. So in the example above, my input would be VARCHAR(11), and I would check for input of length 11. Any input of 11 or more in length would be caught by this check. It works, but it feels wrong. Ideally, the level of data access will automatically detect these problems.
Is there a better way to detect that the input to a stored procedure is longer than allowed? Should you DbCommandalready know the limits of the input length?
Also, how curious is that silence trimming our entrances?