MS SQL: Store Color (ARGB) as integer

The most common way is to store colors in a SQL database as a string, i.e. the HTML color code (ex. #FFFFFF). The colors can also be stored as 32 bit integers. A 32 bit color value (including alpha channel) can be calculated in the following way:

@Alpha * 16777216 + @Red * 65536 + @Green * 256 + @Blue


The full userdefined function is as follows:


-- Description: Generate ARGB Color value
-- =============================================
CREATE FUNCTION [dbo].[sudf_Color_FromArgb]
(
 @intAlpha tinyint,
 @intRed  tinyint,
 @intGreen tinyint,
 @intBlue tinyint
)
RETURNS int
AS
BEGIN

declare @lngColor bigint;
declare @intColor int;

 -- Generate the color
 set @lngColor = (
      (cast(@intAlpha as bigint) * 16777216) +
      (cast(@intRed as bigint) * 65536) +
      (cast(@intGreen as bigint) * 256)  +
      (cast(@intBlue as bigint))
     );

 -- Get the color
 set @intColor = cast(cast(@lngColor as binary(4)) as int);

 -- Return the color
 return @intColor;
END