To convert binary value to bit wise and vice versa

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ConvertBit2Bin2bit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ConvertBit2Bin2bit]
GO

create procedure usp_ConvertBit2Bin2bit @bin varchar(31) = NULL output, @bit int = NULL output
with encryption
AS
/****************************************************************************************************************************************
** Creation Date: June 6, 2004
** Modif. Date: 
** Created By: avigneau
** Database: user
** Description  : To convert binary value to bits like and vice versa.
  
** Parameters   : @type = from bit or from bin, @value = the value to transform 

** Compatibility: SQL Server 7.0, 2000
** Remark: 

** Example: 

declare @bin varchar(128), @bit int
select @bin = '010101010101010101010101010101010101'
exec usp_ConvertBit2Bin2bit @bin output, @bit output
select @bit as " bit",@bin as " bin" 

declare @bin varchar(128), @bit int
select @bit = 80
exec usp_ConvertBit2Bin2bit @bin output, @bit output
select @bit as " bit",@bin as " bin" 

****************************************************************************************************************************************/set nocount on
-- print usage
if @bin is null and @bit is null
begin
print '--Usage for bin to bit:'
print 'declare @bin varchar(128), @bit int'
print 'select @bin = ''0101'''
print 'exec usp_ConvertBit2Bin2bit @bin output, @bit output'
print 'select @bit as " bit",@bin as " bin" '
print ''
print '--Usage for bit to bin:'
print 'declare @bin varchar(128), @bit int'
print 'select @bit = 80'
print 'exec usp_ConvertBit2Bin2bit @bin output, @bit output'
print 'select @bit as " bit",@bin as " bin" '
return
end --if

declare @len int, @cnt int, @crank int

if @bin is not null
begin
set @len = len(@bin)
set @bit = 0
set @cnt = 0
set @crank = 4
while @len > 0
begin
set @crank = @crank * 2
set @cnt = @cnt + 1
if substring(@bin,@cnt,1) = '1'
set @bit = @bit + @crank
else if substring(@bin,@cnt,1) <> '0'
begin
set @bit = 0
break
end
set @len = @len - 1
end -- while
end -- if
if @bit is not null
begin
set @bin = ''
set @crank = 4
while 1=1
begin
set @crank = @crank * 2
if @crank > @bit
break
if @bit&@crank = 0
set @bin = @bin+'0'
else
set @bin = @bin+'1'
end -- while
end -- if
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufn_ConvertBit2Bin2bit]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ufn_ConvertBit2Bin2bit]
GO

create function ufn_ConvertBit2Bin2bit (@type char(3), @value varchar(31))
returns varchar(31)
with encryption
AS
/****************************************************************************************************************************************
** Creation Date: June 6, 2004
** Modif. Date: 
** Created By: avigneau
** Database: user
** Description  : To convert binary value to bits like and vice versa.
  
** Parameters   : @type = from bit or from bin, @value = the value to transform

** Compatibility: SQL Server 7.0, 2000
** Remark: 

** Example: 

select dbo.ufn_ConvertBit2Bin2bit('bin',1111)
select dbo.ufn_ConvertBit2Bin2bit('bit',120)

****************************************************************************************************************************************/begin
-- print usage

declare @len int, @cnt int, @crank int, @retval varchar(31), @bit int

if lower(@type) = 'bin'
begin
set @len = len(@value)
set @bit = 0
set @cnt = 0
set @crank = 4
while @len > 0
begin
set @crank = @crank * 2
set @cnt = @cnt + 1
if substring(@value,@cnt,1) = '1'
set @bit = @bit + @crank
else if substring(@value,@cnt,1) <> '0'
begin
set @bit = 0
break
end
set @len = @len - 1
end -- while
set @retval = cast(@bit as varchar(31))
end
else
begin
set @retval = ''
set @crank = 4
set @bit = cast(@value as int)
while 1=1
begin
set @crank = @crank * 2
if @crank > @bit
break
if @bit&@crank = 0
set @retval = @retval+'0'
else
set @retval = @retval+'1'
end -- while
end -- if
return @retval
end
GO