User-Defined Functions

User-defined functions are somewhat similar to stored procedures. However, 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:

 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 similar 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:

 SELECT dbo.CalcHash(ColumnName) AS Hash  FROM Table