When binary values come as characters

When importing data into SQL through ODBC you might stumble upon a slight hiccup - binary data type is not one of the Microsoft Jet or ODBC data types.

The following procedure treats your binary values as character data, but returns them in the correct data type.

create proc dbo.CharToBin
 (
 @CharacterValue varchar(8000)
 ,@BinaryValue varbinary(8000)  = null  output
 )
as
declare @sql  nvarchar(4000)
declare @params  nvarchar(4000)

set @sql = N'select @BinaryValue = '
         + isnull(@CharacterValue, 0)

set @params = N'@BinaryValue varbinary(8000) output'

exec dbo.sp_executesql
  @stmt = @sql
  ,@parameters = @params
  ,@BinaryValue = @BinaryValue output
go

An example of use:

declare @BinaryValue varbinary(8000)

exec dbo.CharToBin
  @CharacterValue = '0x24806ff96b02a3a1f16cb2840598b236'
  ,@BinaryValue = @BinaryValue output

select @BinaryValue as BinaryValue

ML