User-Defined Functions

User-defined functions are somewhat similar to stored procedures. The big difference is that user-defined functions can be invoked within SQL statements.

Creating User-Defined Functions

A CLR user-defined function can be defined with the attribute [SqlFunction]. The sample function CalcHash() converts the string that is passed to a hashed string. The MD5 algorithm that is used for hashing the string is implemented with the class MD5CryptoServiceProvider from the namespace System.Security.Cryptography. The ComputeHash() method computes the hash from the byte array input and returns a computed hash byte array. The hashed byte array is converted back to a string by using the StringBuilder class:

  using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; using System.Security.Cryptography; public partial class UserDefinedFunctions {    [SqlFunction]    public static SqlString CalcHash(SqlString value)    {       byte[] source;       byte[] hash;       source = ASCIIEncoding.ASCII.GetBytes(value.ToString());       hash = new MD5CryptoServiceProvider().ComputeHash(source);       StringBuilder output = new StringBuilder(hash.Length);       for (int i = 0; i < hash.Length - 1; i++)       {          output.Append(hash[i].ToString("X2"));       }       return new SqlString(output.ToString());    } } 

Using User-Defined Functions

A user-defined function can be deployed with SQL Server 2005 very similarly to the other .NET extensions: either with Visual Studio 2005 or with the CREATE FUNCTION statement:

  CREATE FUNCTION CalcHash (    @value nvarchar ) RETURNS nvarchar AS EXTERNAL NAME Demo.UserDefinedFunctions.CalcHash 

A sample usage of the CalcHash() function is shown with this SELECT statement where the credit card number is accessed from the CreditCard table in the AdventureWorks database by returning just the hash code from the credit card number:

  SELECT Sales.CreditCard.CardType AS [Card Type],    dbo.CalcHash(Sales.CreditCard.CardNumber) AS [Hashed Card] FROM Sales.CreditCard INNER JOIN Sales.ContactCreditCard ON    Sales.CreditCard.CreditCardID = Sales.ContactCreditCard.CreditCardID WHERE Sales.ContactCreditCard.ContactID = 11 

The result returned shows the hashed credit card number for contact ID 11:

 Card Type     Hashed Card ColonialVoice 7482F7B4E613F71144A9B336A3B9F6