SQL - safely disabled BIGINT before INT

Question

I have a CSV that I import into our database. One of the “columns” contains data that must be INT, but some rows have numbers that fall only in the BIGINT range (because this is test data from one of our partners). We keep INT inside of us and do not want to change.

I want to safely bring down BIGINT in INT. Safely, I mean that errors should not occur if arithmetic overflow occurs. If the cast / conversion succeeds, I want my script to continue. If he fails, I want him to be shorted. I can not imagine the correct syntax. This is what I have:

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 thought about comparing @UserIDBigInt with a valid INT range (-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2 147 483 647)), but I really don't like this approach. This is my failure. I was hoping for some language constructs or built-in functions that I could use. If I absolutely need to compare with a valid range, are there at least some built-in constants (e.g. C # int.MinValue and int.MaxValue)?

EDIT : typo fixed.

Answer 1

Add them to your script:

 SET ARITHABORT OFF; SET ARITHIGNORE ON; 

This will convert any overflow values ​​to NULL.

Additional information here: http://msdn.microsoft.com/en-us/library/ms184341.aspx

Answer 2

Set bigint to varbinary , then save the bottom half to @UserID and check the top half:

• if the top half is 0 and the bottom half is a non-negative value, @UserID then contains the correct int value;

• if the top half is only 1 and @UserID negative, everything is fine;

• otherwise there is arithmetic overflow.

Here's the 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 3

I am not sure if this is the best answer, but this is the one I came up with earlier myself. You can 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 4

You can also convert the value to a string, trim it to length and convert to int. not the best way, but a safe easy way for sure