Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to apply it to procedure definitions.

My basic idea is to split the string of type nvarchar(max) into chunks of nvachar(4000) and concatenate the results of HashBytes('MD5',chunk).

I can't imagine that this is not done yet.

share|improve this question
    
Why not to hash the whole string? –  Oleg Dok Jan 4 '12 at 9:46
    
@Oleg Because you get the following error Msg 8152, Level 16, State 10, Line 1 String or binary data would be truncated. –  bernd_k Jan 4 '12 at 9:58
    
So, attached the answer –  Oleg Dok Jan 4 '12 at 10:53
add comment

1 Answer

up vote 4 down vote accepted

If you insist, then

CREATE FUNCTION dbo.GetMyLongHash(@data VARBINARY(MAX))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @res VARBINARY(MAX) = 0x
    DECLARE @position INT = 1, @len INT = DATALENGTH(@data)

    WHILE 1 = 1
    BEGIN
        SET @res = @res + HASHBYTES('MD5', SUBSTRING(@data, @position, 8000))
        SET @position = @position+8000
        IF @Position > @len 
          BREAK
    END
    RETURN @res
END

BUT may be better to use this CLR function and hash data of any length into real MD5 hash?

using System;
using System.Data.SqlTypes;
using System.IO;

namespace ClrHelpers
{
    public partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static Guid HashMD5(SqlBytes data) {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            md5.Initialize();
            int len = 0;
            byte[] b = new byte[8192];
            Stream s = data.Stream;
            do {
                len = s.Read(b, 0, 8192);
                md5.TransformBlock(b, 0, len, b, 0);
            } while(len > 0);
            md5.TransformFinalBlock(b, 0, 0);
            Guid g = new Guid(md5.Hash);
            return g;
        }
    };
}
share|improve this answer
    
There were an error about empty data - I changed it –  Oleg Dok Jan 4 '12 at 12:06
1  
Your solution requires a cast like, Select dbo.GetMyLongHash(cast(definition as varbinary(max))) from sys.sql_modules where object_id = object_id('<myproc>'), but that is a little inconvenience for keeping it more general. Yes and your version regards differences in trailing blanks. –  bernd_k Jan 4 '12 at 12:14
    
@bernd_k oops, thanks, corrected –  Oleg Dok Jan 4 '12 at 12:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.