Solution to the problem of error reporting when bigint to int with sign in SQL Server

Time:2021-6-13

One requirement is to store multiple states (including all kinds of exception and warning states that can exist at the same time) in a cloud monitoring state value. Bit operation mechanism is used to store multiple states in an int.

At present, the amount of monitoring log data is very large (100 million level), and the data needs to be aggregated every hour and every day for online reports.
The state is divided into three levels: normal (0), warning (1) and exception (2). When aggregating, you need to use Max to select the worst state, so you need to process the state value, add the level and the number of state bits, and you need to use bigint type to do the operation,

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

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

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

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