Convert Any Integer Type to a Hex String

Here's a user-defined function to take any integer from a TINYINT to a BIGINT and return it as a hex number in a VARCHAR like '0x01033FA2':

-----------------------------------------------------------------------------
-- Drop any existing function.
-----------------------------------------------------------------------------

IF OBJECT_ID('dbo.Hexadecimal') IS NOT NULL
BEGIN
   RAISERROR('Dropping user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG
   DROP FUNCTION dbo.Hexadecimal
END


-----------------------------------------------------------------------------
-- Create function to take any integer input and return hex as a string.
-----------------------------------------------------------------------------

RAISERROR('Creating user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG
GO

CREATE FUNCTION dbo.Hexadecimal(@vbInput VARBINARY(255))
RETURNS VARCHAR(18) WITH EXECUTE AS CALLER AS
BEGIN
 DECLARE @sResult   VARCHAR(18)  SET @sResult   = '0x'
 DECLARE @i         INT          SET @i         = 1
 DECLARE @nInputLen INT          SET @nInputLen = DATALENGTH(@vbInput)
 DECLARE @nChar     INT          SET @nChar     = 0
 DECLARE @nHiInt    INT          SET @nHiInt    = 0
 DECLARE @nLoInt    INT          SET @nLoInt    = 0

 WHILE (@i <= @nInputLen)
 BEGIN
  SET @nChar  = CONVERT(INT, SUBSTRING(@vbInput, @i, 1))
  SET @nHiInt = FLOOR(@nChar / 16)
  SET @nLoInt = @nChar - (@nHiInt * 16)

  SET @sResult = @sResult +
       SUBSTRING('0123456789ABCDEF', @nHiInt + 1, 1) +
       SUBSTRING('0123456789ABCDEF', @nLoInt + 1, 1)
  SET @i = @i + 1
 END

 RETURN @sResult
END

GO

RAISERROR('Created  user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG


/* Testing.
declare @return char(18)  set @return = ''
declare @bit    bit       set @bit    = 1
declare @tiny   tinyint   set @tiny   = 255
declare @small  smallint  set @small  = 32767
declare @int    int       set @int    = 2147483647
declare @big    bigint    set @big    = 9223372036854775807

print '@bit   maximum is: ' + dbo.Hexadecimal(@bit)
print '@tiny  maximum is: ' + dbo.Hexadecimal(@tiny)
print '@small maximum is: ' + dbo.Hexadecimal(@small)
print '@int   maximum is: ' + dbo.Hexadecimal(@int)
print '@big   maximum is: ' + dbo.Hexadecimal(@big)
-- End testing. */