T-SQL Alternative to hexadecimal binary strings?

I found an easy way to have binary parameters as base64 encoded string. You might wonder, why bother?

Well, in a a well used environment, size and compactness of data over the wire, still matters! Because a binary value is sent as a hexadecimal over the wire; Hexadecimals are 4 times the size of one byte. Base64 encoded strings however, just need +/- 3 times the size of one byte. .


EXEC proc_receiveMyBlob 0xA05FDAF  (etc) 

 The stored procedure itself would have this signature:

CREATE PROC  @myBLob varbinary(max)    -- or image whatever


   INSERT INTO tblMyBlobs VALUES(@myBlob);


The trick:

 CREATE PROC  @myBLob xml  -- <-- use the xml T-SQL data type


-- remember, the binary field in SQL must not be changed to xml, keep it as binary! 

   INSERT INTO tblMyBlobs VALUES(@myBlob.value('xs:base64Binary(.)', 'varbinary(max)') );



The call to the stored proc (obviously) looks something like this:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0' 

or if you like:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0


blog comments powered by Disqus