Good Day Sir/Madame! ^_^
I'm quite a novice when it comes to sql, so please forgive my ignorance.
I have quite a large nvarchar which I wish to pass to the HashBytes function. I get the error:
"String or binary would be truncated. Cannot insert the value NULL into column 'colname', tbale 'table'; column does not allow nulls. UPDATE fails. The statement has been terminated."
Being ever resourceful, I discovered this was due to the HashBytes function having a maximum limit of 8000 bytes. Further searching showed me a 'solution' where my large varchar would be divided and hashed seperately and then later combined with this user defined function:
function [dbo].[udfLargeHashTable] (@algorithm nvarchar(4), @InputDataString varchar(MAX))
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE
@Index int,
@InputDataLength int,
@ReturnSum varbinary(max),
@InputData varbinary(max)
SET @ReturnSum = 0
SET @Index = 1
SET @InputData = convert(binary,@InputDataString)
SET @InputDataLength = DATALENGTH(@InputData)
WHILE @Index <= @InputDataLength
BEGIN
SET @ReturnSum = @ReturnSum + HASHBYTES(@algorithm, SUBSTRING(@InputData, @Index, 8000))
SET @Index = @Index + 8000
END
RETURN @ReturnSum
END
which I call with:
set @ReportDefinitionHash=convert(int,dbo.[udfLargeHashTable]('SHA1',@ReportDefinitionForLookup))
Where @ReportDefinitionHash is int, and @ReportDefinitionForLookup is the varchar
Passing a simple char like 'test' produces a different int with my UDF than a normal call to HashBytes would produce.
Any advice on this issue?
Regards, Byron Cobb.