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.

There are no built-in constants. Should you end up wanting to compare to constants, that question has been answered before: http://stackoverflow.com/questions/7092774/max-value-represented-by-bigint/7092844#7092844 — Sam DeHaan, Aug 17 '11 at 18:09
Why not just assign the value to an INT variable in your stored procedure, assign that value right back to a BIGINT variable, then compare the BIGINT to the original value (also a BIGINT). If there would be overflow assigning to an INT, the values won't match. — Conspicuous Compiler, Aug 17 '11 at 18:54
@Conspicuous Compiler, I never said I'm using a stored procedure. Regardless, doing so raises an "Arithmetic overflow error converting expression to data type int." as alluded to in my original question. — Olson.dev, Aug 18 '11 at 00:30
Please remove some of these useless tags that nobody will be following and replace with RDBMS and version. Assuming MS SQL Server the only language feature I am aware of that might help is `SET ARITHABORT OFF; SET ANSI_WARNINGS OFF;` — Martin Smith, Aug 18 '11 at 00:35
"I want to safely downcast from BIGINT to INT. By safely, I mean no errors should be raised if an arithmetic overflow happens." What does this mean? What should the result be - max int value? nothing happens? — Derek, Aug 18 '11 at 00:48
@Martin Smith, That did the trick. Curious - why wouldn't you post that as an answer instead of a comment? — Olson.dev, Aug 18 '11 at 00:53
@Olson Had presumed this was a MySQL question since there was no tag at the time indicating what flavor you were using, in which case declaration of variables would have implied a stored procedure. My bad. In the case, you would have needed to disable strict SQL mode (`set sql_mode=''`) which I had presumed you had already. — Conspicuous Compiler, Aug 18 '11 at 02:33
@Olson - Because at the time I posted the comment it wasn't confirmed what RDBMS you were using. RE: Version Denali has some new features such as `try_convert` unlikely to be of use to you at the moment though. — Martin Smith, Aug 18 '11 at 22:05

Chains · Accepted Answer · 2011-08-18T15:11:19.007

6

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

edited Aug 18 '11 at 15:11

answered Aug 18 '11 at 00:57

Chains

12,541
8
45
62

Andriy M · Answer 2 · 2011-08-18T05:41:12.957

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

score 1 · Answer 3 · answered Aug 18 '11 at 04:04

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'

score 0 · Answer 4 · answered Aug 18 '11 at 00:45

0

You could 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

answered Aug 18 '11 at 00:45

Thundersquirrel

39
2

SQL - safely downcast BIGINT to INT

4 Answers4

Linked