IP Numeric To String

/**
 * Returns a dot notation IP address such as XXX.XXX.XXX.XXX
 * From a 32 bit unsigned integer.
 * 
 * Author: Gary Feltham <gary.feltham@fpro.co.uk>
 * 
 * SQL Server does not use unsigned int values, the ip storage has
 * been promoted to a bigint (64 bit) value.
 */CREATE FUNCTION dbo.ipNumericToString 
(
@ip bigint
)
RETURNS varchar(15)
AS

BEGIN

DECLARE 
@octetA binary(1), -- individual octets
@octetB binary(1),
@octetC binary(1),
@octetD binary(1),
@str varchar(15), -- the resulting string
@b binary(4) -- the binary value

SET @b = CAST(@ip AS binary(4))

set @octetA = SUBSTRING(@b, 1, 1)
set @octetB = SUBSTRING(@b, 2, 1)
set @octetC = SUBSTRING(@b, 3, 1)
set @octetD = SUBSTRING(@b, 4, 1)

-- need to cast first to a tiny int then to a
string, casting immediately will
-- set the byte value of a char not the numerics
set @str = 
CAST(CAST(@octetA AS tinyint) AS varchar(3)) + '.' +
CAST(CAST(@octetB AS tinyint) AS varchar(3)) + '.' +
CAST(CAST(@octetC AS tinyint) AS varchar(3)) + '.' +
CAST(CAST(@octetD AS tinyint) AS varchar(3))

RETURN @str
END