MS SQL SHASum Support

30. January 2011 15:02

Adding a SHASUM function to MS SQL


public partial class UserDefinedFunctions
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]
    public static SqlString SHASum(SqlString S1)
        byte[] buffer = Encoding.ASCII.GetBytes(S1.Value);
        SHA1CryptoServiceProvider cryptoTransformSHA1 =
        new SHA1CryptoServiceProvider();
        string hash = BitConverter.ToString(
            cryptoTransformSHA1.ComputeHash(buffer)).Replace("-", "");

        return new SqlString(hash);

You will also need the following to install the function


CREATE Function SHASum(@S1 nvarchar(512))
	RETURNS nvarchar(64) AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.MD5Sum

You should be able to call the function in normal sql code like SELECT dbo.SHASum('test value')

More about enabling .NET CLR In SQL Server

E-mail Kick it! DZone it! Permalink

Pingbacks and trackbacks (1)+