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