title: SQL2000 safely cast a VARCHAR(256) to INT
body: <p>I'm having some problem safely casting a <code>varchar</code> to <code>int</code> on SQL2000.</p>
  
  <p>Part 1 of my problem was that <a href="http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/" rel="nofollow noreferrer">IsNumeric returns false positives</a> if your looking for integers only. I'm aware though why IsNumeric does this though (floats, money etcetera are numeric too) so i looked for an <code>IsInteger</code> function on google. </p>
  
  <p>I found the following User Defined Function (UDF):</p>
  
  <pre><code>CREATE FUNCTION dbo.IsInteger
  (  
      @num VARCHAR(64)  
  )  
  RETURNS BIT  
  BEGIN  
      IF LEFT(@num, 1) = '-'  
          SET @num = SUBSTRING(@num, 2, LEN(@num))  
  
      RETURN CASE  
      WHEN PATINDEX('%[^0-9-]%', @num) = 0  
          AND CHARINDEX('-', @num) &lt;= 1  
          AND @num NOT IN ('.', '-', '+', '^') 
          AND LEN(@num)&gt;0  
          AND @num NOT LIKE '%-%' 
      THEN  
          1  
      ELSE  
          0  
      END  
  END
  </code></pre>
  
  <p>this seems to do a good job checking for integers:</p>
  
  <pre><code>declare @num varchar(256);
  declare @num2 varchar(256);
  set @num = '22312311';
  set @num2 = '22312311.0';
  SELECT  @num AS [character], 
   dbo.IsInteger(@num) AS [isInteger], 
   CASE dbo.IsInteger(@num)WHEN 1 THEN convert(int, @num) ELSE NULL END AS [integer]
  UNION
  SELECT  @num2 AS [character], 
   dbo.IsInteger(@num2) AS [isInteger], 
   CASE dbo.IsInteger(@num2)WHEN 1 THEN convert(int, @num2) ELSE NULL END AS [integer];
  </code></pre>
  
  <p>However it won't validate if the integer is within range (<code>-2^31 &lt;=&gt; 2^31 - 1</code>)</p>
  
  <pre><code>declare @num varchar(256);
  set @num = '2147483648';
  SELECT  @num AS [character], 
   dbo.IsInteger(@num) AS [isInteger], 
   CASE dbo.IsInteger(@num)WHEN 1 THEN convert(int, @num) ELSE NULL END AS [integer];
  </code></pre>
  
  <p>Which throws </p>
  
  <p><code>Server: Msg 248, Level 16, State 1, Line 3<br>
  The conversion of the nvarchar value '2147483648' overflowed an int column. Maximum integer value exceeded.</code></p>
  
  <p><a href="https://stackoverflow.com/questions/33685/sql-2000-try-catch-like-error-handling#39161">SQL2000 doesn't have TRY/CATCH</a> (answer presumes ISNUMERIC() returns no false positives) and casting errors cause the entire batch to fail even within UDF's according to <a href="http://www.sommarskog.se/error-handling-II.html" rel="nofollow noreferrer">this website</a>:</p>
  
  <blockquote>
    <p>When an error occurs in a UDF,
    execution of the function is aborted
    immediately and so is the query, and
    unless the error is one that aborts
    the batch, execution continues on the
    next statement – but @@error is 0!</p>
  </blockquote>
  
  <p>and even if they didn't would still obscure <code>@@error</code>. I also can't cast to <code>bigint</code> since it might still crash (albeit not as often) and this query is part of a UNION which is output to XML which is further validated and transformed with XSLT by a VB6 COM DLL and displayed on a website coded back in 2001 so I really (no <strong>really</strong>) do not want to change the query output!.</p>
  
  <p>So this leaves me stuck on this seemingly easy task:   </p>
  
  <p><strong>if varchar is castable to int cast to int otherwise give me NULL</strong></p>
  
  <p>Any pointers / solutions would be much apreciated but please note that I can't, under no circumstance, change the source column's datatype nor change the validation when data is entered.</p>