6

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?

Nick Chammas
  • 11,843
  • 8
  • 56
  • 115
  • Can you get rid of the character limits and use a CHECK constraint instead? – Mike Christensen Jan 23 '12 at 21:52
  • @MikeChristensen - Can you expand a little on what you mean? Our tables are already constrained appropriately with the right data types (and lengths), FKs, and `CHECK` constraints. What I am concerned with is detecting over-limit inputs before we use them in any stored procedure logic or queries. – Nick Chammas Jan 23 '12 at 21:57
  • Oh I apologize, I was thinking you were directly inserting into a table. You're talking about sproc parameters. Can you just make it a `VARCHAR(MAX)` and then check the length in the sproc? – Mike Christensen Jan 23 '12 at 22:02
  • can there be spaces..?? and if not Nick then use the Trim function – MethodMan Jan 23 '12 at 22:05
  • @MikeChristensen - Yep. All you need is `VARCHAR(n+1)`, where `n` is the proper length of the input. – Nick Chammas Jan 23 '12 at 22:05
  • @NickChammas - To answer your question, yes parameter truncation is by design and no, SQL Server will not change this any time soon. You'll have to do this logic yourself. Personally, I'd use `MAX` if it's just a parameter but `n+1` works as well. – Mike Christensen Jan 23 '12 at 22:08
  • It's SQLServer that's doing the truncation. No good answer on detection beyond making sure you set your varchar limits according to logical constraints rather than as an "optimisation", but I'm guessing I might be preaching to the choir on that one if you've been struggling with it. – Jon Hanna Jan 23 '12 at 22:10
  • 1
    Also helpful: http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures – Mike Christensen Jan 23 '12 at 22:12

1 Answers1

3

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

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569