SQL2000 safely cast a VARCHAR(256) to INT

Question

I'm having some problem safely casting a varchar to int on SQL2000.

Part 1 of my problem was that IsNumeric returns false positives 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 IsInteger function on google.

I found the following User Defined Function (UDF):

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) <= 1  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
    THEN  
        1  
    ELSE  
        0  
    END  
END

this seems to do a good job checking for integers:

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];

However it won't validate if the integer is within range (-2^31 <=> 2^31 - 1)

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];

Which throws

Server: Msg 248, Level 16, State 1, Line 3
The conversion of the nvarchar value '2147483648' overflowed an int column. Maximum integer value exceeded.

SQL2000 doesn't have TRY/CATCH (answer presumes ISNUMERIC() returns no false positives) and casting errors cause the entire batch to fail even within UDF's according to this website:

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!

and even if they didn't would still obscure @@error. I also can't cast to bigint 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 really) do not want to change the query output!.

So this leaves me stuck on this seemingly easy task:

if varchar is castable to int cast to int otherwise give me NULL

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.

Answer 1

Edit:

You can not have numbers over decimal(38,0) in SQL Server (+/- 10^38 -1) so can not trap them or convert them. Which means 37 characters may length and a CAST to decimal(38,0)

SELECT
    CASE
        WHEN CAST(MyColumn AS decimal(38,0) BETWEEN -2147483648 AND 2147483647 THEN  CAST(MyColumn AS int)
        ELSE NULL
    END
FROM
    MyTable
WHERE
    ISNUMERIC(MyColumn + '.0e0') = 1 AND LEN(MyColumn) <= 37

Respect to this article for the .0e0 trick

EDIT OP
This question lead me to the folowing updated IsInteger function.

CREATE FUNCTION dbo.IsInteger
(  
    @num VARCHAR(256)  
)  
RETURNS BIT
BEGIN    
    RETURN CASE 
            WHEN ISNUMERIC(@num + '.0e0') = 1  AND convert(decimal(38,0), @num) BETWEEN -2147483648 AND 2147483647 THEN  1
            ELSE 0
    END
END

Answer 2

You could just add a couple more checks into the function:

CREATE FUNCTION [dbo].[IsInteger] 
(   
    @num VARCHAR(64)   
)   
RETURNS BIT   
BEGIN   
    IF LEFT(@num, 1) = '-'   
        SET @num = SUBSTRING(@num, 2, LEN(@num))   

    DECLARE @IsInt BIT

    SELECT @IsInt = CASE   
    WHEN PATINDEX('%[^0-9-]%', @num) = 0   
        AND CHARINDEX('-', @num) <= 1   
        AND @num NOT IN ('.', '-', '+', '^')  
        AND LEN(@num)>0   
        AND @num NOT LIKE '%-%'  
    THEN   
         1
    ELSE   
         0
    END   

    IF @IsInt = 1
        BEGIN

            IF LEN(@num) <= 11
                BEGIN
                    DECLARE @test bigint
                    SELECT @test = convert(bigint, @num)
                    IF @test <= 2147483647 AND @test >= -2147483648
                        BEGIN
                            set @IsInt = 1
                        END
                    ELSE
                        BEGIN
                            set @IsInt = 0
                        END
                END
            ELSE
                BEGIN
                    set @IsInt = 0
                END
        END


    RETURN @IsInt

END 

I've not had a chance to test but I think it should work - I've left it as verbose as possible