Hi All,

I'm having a bit of an issue with a stored procedure I have written which encrypts a value using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE.

The problem is these functions return Varbinary data types which I need to append to a Varchar data type.

Can anyone help with being able to display this as a Varchar data type without converting it? ie have a Varchar whose value is "0x010000004C4101804DD79FC3CA2258826140B2A3B66A0130F4AECB34" rather than the actual Varchar conversion of the Varbinary.

I'm not limited to using ENCRYPTBYPASSPHRASE if anyone knows of a more convenient method. Please bear in mind the values are stored in plaintext so encrypting the columns is the only no-no.



The master database has a scalar function, fn_varbintohexstr, for this.


DECLARE @text varchar(256), @varbinary varbinary(256);
SET @text = 'A1B2C3';
SET @varbinary = cast(@text as varbinary(256));

     @text OriginalString
    , sys.fn_varbintohexstr(@varbinary) HexString /* returns nvarchar(max)*/