SQL - 安全地将 BIGINT 向下转换为 INT [英] SQL - safely downcast BIGINT to INT

问题描述

我有一个 CSV 文件要导入到我们的数据库中.其中一个列"包含应该 INT 的数据，但有些行的数字仅落在 BIGINT 范围内(因为它们是来自我们合作伙伴之一的测试数据).我们在内部存储 INT，不希望更改.

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.

我想安全地从 BIGINT 转换为 INT.安全地说，我的意思是，如果发生算术溢出，不应引发任何错误.如果转换/转换成功，我希望我的脚本继续运行.如果失败，我希望它短路.我似乎无法弄清楚正确的语法.这就是我所拥有的:

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

我曾考虑将@UserIDBigInt 与 INT 的有效范围(-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647))进行比较，但我真的不喜欢这种方法.那是我的退路.我希望有一些我可以使用的语言结构或内置函数.如果我绝对必须与有效范围进行比较，是否至少有一些内置常量(例如 C# 的 int.MinValue & int.MaxValue)?

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)?

编辑:更正错字.

推荐答案

将这些添加到您的脚本中:

Add these to your script:

SET ARITHABORT OFF;
SET ARITHIGNORE ON;

这会将任何溢出值转换为 NULL.

This will convert any overflow values to NULL.

更多信息:http://msdn.microsoft.com/en-我们/图书馆/ms184341.aspx

这篇关于SQL - 安全地将 BIGINT 向下转换为 INT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！