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.

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Dec 7, 2017 at 6:23
That is a bit complicated. What should return this function? On 11 output should be 1011? — gofr1
– gofr1, Commented Dec 7, 2017 at 6:33
yes (gofr1). Appreciate the response. I know and not sure I understand the issue of using constants there but was told it is not self-explanatory . — vbNewbie
– vbNewbie, Commented Dec 7, 2017 at 6:35
marc_s - this is legacy procedures that I have to work with and not in a position to change but can suggest changes. So thanks for that. — vbNewbie
– vbNewbie, Commented Dec 7, 2017 at 6:36
I think, it's already answered here stackoverflow.com/questions/42544431/… — Jayasurya Satheesh
– Jayasurya Satheesh, Commented Dec 7, 2017 at 6:49

gofr1 · Accepted Answer · 2017-12-07 06:49:31Z

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:

answered Dec 7, 2017 at 6:49

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

vbNewbie Over a year ago

Thanks gofr1. How do I assign the select to the variable @StoreFlagsBin. @StoreFlagsBin= REVERSE(@StoreFlagsBin)

gofr1 Over a year ago

SELECT @StoreFlagsBin= REVERSE(@StoreFlagsBin) or SET...

Nicolaesse Over a year ago

It seems to not work properly. Int(40) should be 110000 and not 101000

gofr1 Over a year ago

@Nicolaesse check it please in any online converter: 40 decimal will be 101000 in binary. 48 decimal = 110000 binary.

Nicolaesse Over a year ago

@gifr1 sorry for my comment, I double check and you're completely right.

Collectives™ on Stack Overflow

Using decimal to binary conversion in SQL Server stored procedure

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related