Preventing Sql column overflows

Question

If I have a parametrized stored procedure which takes a varchar(10) value and converts it to an int, I currently have to ensure that the value is not greater than the varchar equivalent of the maximum int value.

IF @Criteria <= '2147483647'
 SET @Id = CONVERT(int, @Criteria)

My question: Is there a better way to prevent overflowing an int column when converting from a varchar value?

Edit: Yes obviously if I felt that a the value was going to legitimately contain something close to the max value I could expand to BigInt. This is really meant to handle improper calls to this stored proc, and was just a general-purpose question about using Convert() in cases where the resulting value might overflow the desired data type.

Answer 1

The easiest and best way is to deal with it at the source, which is wherever the varchar is created. Or else explain what you mean by "prevent overflowing". What do you expect to happen when the varchar is in fact too long?

Answer 2

What if you pass in spaces? - it will pass the IF condition but fail on the conversion. You should use ISNUMERIC () as well.

IF @Criteria <= '2147483647' AND ISNUMERIC(@Criteria)
        SET @Id = CONVERT(int, @Criteria)

Answer 3

A better question might be why you are storing, as varchars, integers that would overflow their destination column. I'm not too sure what you can do to prevent overflows completely; you may consider switching Id to an unsigned integer, so you can get up to 2^32 bits available (I am guessing Criteria is never negative, as you are assigning it to an Id column).

I'm not sure if SQL Server supports it, but MySQL has BIGINT columns, which go up to 2^64 (2^63 if you want it signed).

Answer 4

To deal with the various conditions (spaces, decimals etc) wrap the convert in a TRY/CATCH if you can't clean on the client. Assumes SQL Server 2005

...
BEGIN TRY
   SET @Id = CONVERT(int, @Criteria)
END TRY
BEGIN CATCH
   SET @Id = NULL
END CATCH
...

Answer 5

Your test won't work reliably.

If @Criteria contains '11111111111111111111', it sorts less than your magic number, because you are doing a string comparison.

Answer 6

First, your input length of 10 looks like you don't expect (or accept) negative values. The low bound for int is -2147483648, which would be represented by an 11-character string.

Building on DJ's code from above, I suggest you put the ISNUMERIC() call before the CONVERT/compare.

IF ISNUMERIC(@Criteria) = 1
  AND CONVERT(bigint, @Criteria) <=  2147483647
        SET @Id = CONVERT(int, @Criteria)

This converts to a bigint first and then compares. Here are a few test cases:

DECLARE @Id         int
DECLARE @Criteria   varchar(10)

PRINT 'Expect failure (NULL)'
SET @Criteria = '2147483648'
SET @Id = NULL

IF ISNUMERIC(@Criteria) = 1
  AND CONVERT(bigint, @Criteria) <=  2147483647
        SET @Id = CONVERT(int, @Criteria)

SELECT @Id AS '@Id', @Criteria AS '@Criteria', CONVERT(bigint, @Criteria) AS 'Converted to bigint'

PRINT 'Expect success'
SET @Criteria = '2147483647'
SET @Id = NULL

IF ISNUMERIC(@Criteria) = 1
  AND CONVERT(bigint, @Criteria) <=  2147483647
        SET @Id = CONVERT(int, @Criteria)

SELECT @Id AS '@Id', @Criteria AS '@Criteria', CONVERT(bigint, @Criteria) AS 'Converted to bigint'

PRINT 'Expect failure but get success because @Criteria is truncated to 10 characters'
SET @Criteria = '11111111111111111111'
SET @Id = NULL

IF ISNUMERIC(@Criteria) = 1
  AND CONVERT(bigint, @Criteria) <= 2147483647
        SET @Id = CONVERT(int, @Criteria)

SELECT @Id AS '@Id', @Criteria AS '@Criteria', CONVERT(bigint, @Criteria) AS 'Converted to bigint'

and results:

Expect failure (NULL)
        @Id @Criteria   Converted to bigint
----------- ---------- --------------------
       NULL 2147483648           2147483648

Expect success
        @Id @Criteria   Converted to bigint
----------- ---------- --------------------
 2147483647 2147483647           2147483647

Expect failure but get success because @Criteria is truncated to 10 characters
        @Id @Criteria   Converted to bigint
----------- ---------- --------------------
 1111111111 1111111111           1111111111

Note that passing '11111111111111111111' actually works since the input is truncated.