Using decimal to binary conversion in SQL Server stored procedure

Question

I need to be able to convert an int column to binary form. This column represents a combination of bit values to store multiple values in one column. I found a solution that creates a function to change an int value to its binary form but I cannot create additional functions in this database and would like to implement it in my procedure as is.

select @StoreFlags = r.StoreFlags
from insert I, StorageRule r
where r.Active = 1 and I.Active = 0 

PRINT CONVERT(VARBINARY(16),@StoreFlags)

IF(@StoreFlags = 11)  
    insert into StoreQueue (TimeToExecute, Operation, Parameter, StorageID, StoreFlags)
       select  
           DateAdd(mi, @Time, getutcdate()), 1, I.ID, r.ID, r.StoreFlags 
       from 
           insert I, StorageRule r
       where 
           r.Active = 1 and I.Active = 0 

The print statement above shows where I initially tried to convert to binary but that seems to be in hex form. the function below does what I need but I need to apply it against the variable @StoreFlags and specifically use the returned answer in the If statement instead of an integer constant. Thereafter I need to manipulate the bits to convert it to a new value.

CREATE FUNCTION dbo.Int2Binary (@i INT) 
RETURNS NVARCHAR(16) 
AS 
BEGIN
    RETURN
        CASE WHEN CONVERT(VARCHAR(16), @i & 32768 ) > 0 THEN '1' ELSE '0'     END 
        CASE WHEN CONVERT(VARCHAR(16), @i & 16384 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &  8192 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &  4096 ) > 0 THEN '1' ELSE '0'   END          
        CASE WHEN CONVERT(VARCHAR(16), @i &  2048 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &  1024 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &   512 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &   256 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &   128 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &    64 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &    32 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &    16 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &     8 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &     4 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &     2 ) > 0 THEN '1' ELSE '0'   END 
        CASE WHEN CONVERT(VARCHAR(16), @i &     1 ) > 0 THEN '1' ELSE '0'   END
 END;
 GO

 SELECT dbo.Int2Binary(11)
 GO

Any advice on how to implement this.

EDIT:

Just to clarify; the function will return for 11: 0000000000001011. So I then need to change a single bit there to use in the insert following the If. Which in my case would be to change to the value 9, 0000000000001001.

Answer 1

You can use this query instead of function:

DECLARE @StoreFlags int = 11,
        @StoreFlagsBin nvarchar(100) = N''

WHILE @StoreFlags > 0
BEGIN
    SET @StoreFlagsBin = @StoreFlagsBin + CAST(@StoreFlags%2 as nvarchar(1))
    SET @StoreFlags = @StoreFlags/2
END

SELECT REVERSE(@StoreFlagsBin)

Output:

1011