title: SQL Server silently truncates varchar's in stored procedures
body: <p>According to <a href="http://www.pcreview.co.uk/forums/thread-1241669.php" rel="noreferrer">this forum discussion</a>, SQL Server (I'm using 2005 but I gather this also applies to 2000 and 2008) silently truncates any <code>varchar</code>s you specify as stored procedure parameters to the length of the varchar, even if inserting that string directly using an <code>INSERT</code> would actually cause an error.  eg. If I create this table:</p>
  
  <pre><code>CREATE TABLE testTable(
      [testStringField] [nvarchar](5) NOT NULL
  )
  </code></pre>
  
  <p>then when I execute the following:</p>
  
  <pre><code>INSERT INTO testTable(testStringField) VALUES(N'string which is too long')
  </code></pre>
  
  <p>I get an error:</p>
  
  <pre><code>String or binary data would be truncated.
  The statement has been terminated.
  </code></pre>
  
  <p>Great.  Data integrity preserved, and the caller knows about it.  Now let's define a stored procedure to insert that:</p>
  
  <pre><code>CREATE PROCEDURE spTestTableInsert
      @testStringField [nvarchar](5)
  AS
      INSERT INTO testTable(testStringField) VALUES(@testStringField)
  GO
  </code></pre>
  
  <p>and execute it:</p>
  
  <pre><code>EXEC spTestTableInsert @testStringField = N'string which is too long'
  </code></pre>
  
  <p>No errors, 1 row affected.  A row is inserted into the table, with <code>testStringField</code> as 'strin'.  SQL Server silently truncated the stored procedure's <code>varchar</code> parameter.</p>
  
  <p>Now, this behaviour might be convenient at times but I gather there is NO WAY to turn it off.  This is extremely annoying, as I <em>want</em> the thing to error if I pass too long a string to the stored procedure.  There seem to be 2 ways to deal with this.</p>
  
  <p>First, declare the stored proc's <code>@testStringField</code> parameter as size 6, and check whether its length is over 5.  This seems like a bit of a hack and involves irritating amounts of boilerplate code.</p>
  
  <p>Second, just declare ALL stored procedure varchar parameters to be <code>varchar(max)</code>, and then let the <code>INSERT</code> statement within the stored procedure fail.</p>
  
  <p>The latter seems to work fine, so my question is: is it a good idea to use <code>varchar(max)</code> ALWAYS for strings in SQL Server stored procedures, if I actually want the stored proc to fail when too long a string is passed?  Could it even be best practice?  The silent truncation that can't be disabled seems stupid to me.</p>