SQL - safely downcast BIGINT to INT

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

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

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 CATCHEND CATCHIF @UserID IS NULL BEGIN    SELECT 'Handle it as unreliable data'    RETURNENDSELECT 'Handle it as reliable data'