Binary Number in Sql

SQL Server doesn't have the functionality to read or store binary's out of the box, no. if you're storing a binary, you'd need to do so in a varchar. I.e. DECLARE @bin varchar(4) = '1111';.

Once you've stored the value as a varchar you'd the need to use a function to split the value out. Now, the largest number you can stored (as an integer) in SQL Server is 9223372036854775807, which is 63 digits long in binary (so we'll go for a varchar(64) to store the negative as well).

We can then create a table value function to convert the value from binary to an integer:

CREATE FUNCTION dbo.BinaryToInt (@Binary varchar(64))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

    WITH C0 AS(SELECT 1 AS c UNION ALL SELECT 1),
         C1 AS(SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
         C2 AS(SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
         C3 AS(SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
    N AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM C3)
    SELECT SUM(POWER(2, CASE B.V WHEN '-' THEN NULL ELSE N.I END -1) * B.V) * CASE WHEN MIN(B.V) = '-' THEN -1 ELSE 1 END AS IntegerValue
    FROM N
         CROSS APPLY (VALUES (SUBSTRING(REVERSE(@Binary), N.I, 1))) B(V)
    WHERE B.V <> '';
GO

Finally, we can then Declare the variable and set the value:

DECLARE @Bin int;

SELECT @bin = IntegerValue
FROM dbo.BinaryToInt ('1111');
SELECT @bin;

SELECT @bin = IntegerValue
FROM dbo.BinaryToInt ('-11101');
SELECT @Bin;

Hope that helps.

I realise that real binary numbers don't have a negative, but this was just me short handing.

Edit: Although I realise that the Op said this isn't what they (seemed?) to be asking for, however, for completion I did a signed and unsigned version of the above:

CREATE FUNCTION dbo.UnSignedBinaryToInt (@Binary varchar(63))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

    WITH C0 AS(SELECT 1 AS c UNION ALL SELECT 1),
         C1 AS(SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
         C2 AS(SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
         C3 AS(SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
    N AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM C3)
    SELECT SUM(POWER(2, N.I - 1) * B.V) AS IntegerValue
    FROM N
         CROSS APPLY (VALUES (SUBSTRING(REVERSE(@Binary), N.I, 1))) B(V)
    WHERE B.V <> ''
      AND @Binary NOT LIKE '%[^01]%' --Eliminate values that aren't binary;
GO

CREATE FUNCTION dbo.SignedBinaryToInt (@Binary varchar(64))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

    WITH C0 AS(SELECT 1 AS c UNION ALL SELECT 1),
         C1 AS(SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
         C2 AS(SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
         C3 AS(SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
    N AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM C3),
    I AS (
        SELECT SUM(POWER(2, N.I - 1) * B.V) AS IntegerValue
        FROM N
             CROSS APPLY (VALUES (SUBSTRING(REVERSE(RIGHT(@Binary, LEN(@Binary)-1)), N.I, 1))) B(V)
        WHERE B.V <> ''
          AND @Binary NOT LIKE '%[^01]%' --Eliminate values that aren't binary
    )
    SELECT IntegerValue * CASE LEFT(@binary,1) WHEN 1 THEN -1 ELSE 1 END AS IntegerValue
    FROM I;

GO


SELECT IntegerValue
FROM dbo.UnSignedBinaryToInt ('00001111');
SELECT IntegerValue
FROM dbo.SignedBinaryToInt ('10001111');
SELECT IntegerValue
FROM dbo.UnSignedBinaryToInt ('10001111');
SELECT IntegerValue
FROM dbo.UnSignedBinaryToInt ('00201111');

GO

This returns the values 15, -15, 143 and NULL (due to the 2) respectively.