haiao2000 (10/2/2012)
TheSQLGuru (10/2/2012)
I am seriously missing something:SELECT CAST(5 AS VARBINARY(128)) --0x00000005
SELECT CAST(0x00000005 AS bigint) --5
SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001
SELECT CAST(0x010001 AS bigint) --65537
NAH! you aren't missing anything. it was just our requirements written that way. In the nutshell, our existing application has capability of searching the varbinary field data bit-by-bit (or byte-by-byte whatever that is..) without first converingt the bit to character, that is from what I was told and understood. so this is the how conversion process works:
-first convert the bigint to binary value,
-reverse the entire string
-foreach bit in the string, add a 0 infront of it.
-then convert the value to varbinary field using this format: convert(varbinary(128),@result,2)
Ahh - what I was missing was the actual requirements! 🙂
This may get you part-way there: http://pratchev.blogspot.com/2008/04/convert-hex-string-to-binary-string.html. You will need to efficiently deal with leading zeros though before doing the reverse/replace thing. The key is to do this as a set-based operation and not the iterative way you have now. I am confident there is a solution, but it goes beyond what I am willing to invest for free to get you there. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service