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
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,
@UserIDthen contains the correct
if the upper half is all 1's and
@UserIDis 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.
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'