SQL Server bigint to int signed conversion function (original)

One requirement is to store multiple states (including various exceptions and warning states that can exist) in a cloud monitor storage state value using a bitwise operation mechanism in an int type .

The amount of monitoring log data is now very large (billion levels) need to aggregate data hourly, daily, for online reports to use.
The state is divided into 3 levels: normal (0), Warning (1), exception (2), aggregation needs to use Max to select the worst state, you need to process the state value plus the number of levels and state bits, the use of bigint type to do the operation,

The problem is that when you then convert bigint to int when you get the original state value, SQL Server has an error:

Msg 8115, Level 16, State 2, line 1th
An arithmetic overflow error occurred while converting expression to data type int.

Because 0x80000000has been used in the status code, there is a sign bit problem.
wrote a conversion function solved.

CREATE FUNCTION [dbo].[Biginttoint](     @Value bigint   )RETURNS int asBEGIN     --whether there is an int sign bit     IF @Value & 0x80000000 <> 0 RETURN @Value & 0xFFFFFFFF | 0xffffffff00000000     --unsigned bit     RETURN @Value & 0xFFFFFFFFEND