SQL - safely downcast BIGINT to INT

Question

I have a CSV I'm importing into our database. One of the "columns" contains data that should be an INT but some rows have numbers that only fall in the BIGINT range (because they're test data from one of our partners). We store INT internally and have no desire to change.

I want to safely downcast from BIGINT to INT. By safely, I mean no errors should be raised if an arithmetic overflow happens. If the cast/conversion succeeds, I want my script to go on. If it fails, I want it to short-circuit. I can't seem to figure out the proper syntax. This is what I've got:

DECLARE @UserIDBigInt BIGINT = 9723021913; -- actually provided by query param
--Setting within the INT range successfully converts
--SET @UserIDBigInt = 5;
DECLARE @UserID INT = CONVERT(INT, @UserIDBigInt);
--DECLARE @UserID INT = CAST(@UserIDBigInt AS INT);
SELECT @UserIDBigInt
SELECT @UserID
IF @UserID IS NOT NULL BEGIN
    SELECT 'Handle it as reliable data'
END

I've thought about comparing @UserIDBigInt to the valid range of an INT (-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)), but I really don't like that approach. That's my fallback. I was hoping for some language constructs or built-in functions I could use. If I absolutely have to compare to the valid range, are there at least some built-in constants (like C#'s int.MinValue & int.MaxValue)?

EDIT: Corrected typo.

Answer 1

Cast your bigint to varbinary, then store the lower half to @UserID and check the upper half:

• if the upper half is all 0's and the lower half represents a non-negative value, @UserID then contains the correct int value;

• if the upper half is all 1's and @UserID is negative, it's all right too;

• otherwise there's an arithmetic overflow.

Here's an implementation:

DECLARE @UserIDBigInt BIGINT = 9723021913;
DECLARE @UserID INT, @HighInt INT;

WITH v AS (SELECT CAST(@UserIDBigInt AS varbinary) AS bin)
SELECT
  @HighInt = SUBSTRING(bin, 1, 4),
  @UserID  = SUBSTRING(bin, 5, 4)
FROM v;

IF (@HighInt = 0 AND @UserID >= 0 OR @HighInt = -1 AND @UserID < 0) BEGIN
    SELECT 'Handle it as reliable data'
END

Answer 2

I'm not sure this is the best answer but it is one I came up with earlier on my own. It is possible to catch the exception/error and gracefully continue execution.

Example:

DECLARE @UserIDBigInt BIGINT = 9723021913;
DECLARE @UserID INT;
BEGIN TRY
    SET @UserID = @UserIDBigInt;
END TRY BEGIN CATCH
END CATCH

IF @UserID IS NULL BEGIN
    SELECT 'Handle it as unreliable data'
    RETURN
END

SELECT 'Handle it as reliable data'

Answer 3

Add these to your script:

SET ARITHABORT OFF;
SET ARITHIGNORE ON;

This will convert any overflow values to NULL.

More info here: http://msdn.microsoft.com/en-us/library/ms184341.aspx