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)