A function to solve the error reporting problem of bigint to int signed in SQL server

Time:2021-9-22

One requirement is to store multiple states (including various exceptions and warning states that can exist at the same time) in a cloud monitoring state value. The bit operation mechanism is used to store them in an int type.

At present, the amount of monitoring log data is very large (100 million level). The data needs to be aggregated hourly and daily for online reports.
There are three levels of status: normal (0), warning (1) and exception (2). When aggregating, you need to use Max to select the worst status, so you need to process the status value. Add the level and the number of status bits, and you need to use bigint type for operation,

The problem is that when getting the original status value when bigint is converted to int, SQL server reports an error:

Message 8115, level 16, status 2, line 1
Arithmetic overflow error converting expression to data type int.

Because 0x80000000 has been used in the status code, there is a sign bit problem.
Write a conversion function to solve it.

CREATE FUNCTION [dbo].[BigintToInt]
(
   @Value bigint  
)
RETURNS int
AS
BEGIN
   --Is there an int sign bit
   IF @Value & 0x80000000 <> 0 RETURN @Value & 0xFFFFFFFF | 0xFFFFFFFF00000000
   --Unsigned bit
   RETURN @Value & 0xFFFFFFFF
END