Conversion From Decimal to Binary in SQL Server 2005.

Below is the function that we use to convert decimal to binary number.


Create FUNCTION dbo.udf_GetNumbers (@base INT, @lenght INT)
RETURNS @baseN_numbers TABLE
(
decNum INT PRIMARY KEY NOT NULL,
baseN_Num VARCHAR(50) NOT NULL
)
AS
BEGIN
WITH tblBase AS
(
SELECT CAST(0 AS VARCHAR(50)) AS baseNum
UNION ALL
SELECT CAST((baseNum + 1) AS VARCHAR(50))
FROM tblBase WHERE baseNum < @base-1
),
numbers AS
(
SELECT CAST(baseNum AS VARCHAR(50)) AS num FROM tblBase
UNION ALL
SELECT CAST((t2.baseNum + num) AS VARCHAR(50)) FROM numbers CROSS JOIN tblBase t2
WHERE LEN(NUM) < @lenght
)
INSERT INTO @baseN_numbers
SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
FROM numbers WHERE LEN(NUM) > @lenght - 1
OPTION (MAXRECURSION 0);
RETURN
END



select * FROM dbo.udf_GetNumbers(2, 8)

will display the 0 to 255 decimal and binary numbers.