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

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 2

Your test won't work reliably.

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