mardi 19 juillet 2016

Are there any side-effects of converting a VARBINARY to a VARCHAR?

I was writing a FUNCTION to wrap around a call to HASHBYTES, and had two parameters, a VARCHAR(MAX) one and a VARBINARY(MAX) one. The intention being that the user will call this to hash a VARCHAR value or a VARBINARY value.

However, in testing I noticed that I could pass a VARBINARY value to the VARCHAR parameter and have it get quietly and implicity converted to VARCHAR.

CREATE FUNCTION fnHashBigField( @varcharInput   AS VARCHAR(MAX)     = NULL,
                                @varbinaryInput AS VARBINARY(MAX)   = NULL) RETURNS VARCHAR(900)
BEGIN

    DECLARE @initialValue AS VARCHAR(MAX)
    SET @initialValue = ''  

    IF @varbinaryInput IS NOT NULL
    BEGIN
        SET @initialValue = '0x' + cast('' AS XML).value('xs:hexBinary(sql:variable("@varbinaryInput") )', 'VARCHAR(MAX)');
    END
    ELSE
    BEGIN
        SET @initialValue = @varcharInput
    END

    --Code to split up the VARCHAR into chunks small enough for HASHBYTES to work with

    RETURN ''

END

From research, I've noticed that the conversion will implicitly work from VARBINARY to VARCHAR, but not the other way around. I've also noted on SQL Server 2005 that if you try to CONVERT a VARBINARY to VARCHAR, it will give you what appears to be a text version of the raw data, rather than a hex string like in later versions of SQL Server. Thus I suspect that I would still need to keep the XQuery conversion code.

My question is this: Are there any side-effects to this implicit conversion from VARBINARY to VARCHAR? If there's no issues I can simply have one VARCHAR(MAX) parameter.

Aucun commentaire:

Enregistrer un commentaire