ADO.NET: Building C# User-Defined Functions in SQL Server

A function in SQL Server is just that: a function that can be invoked from within any T-SQL statement that will return a value. SQL Server has many built-in functions for string concatenation, mathematics, data type conversion, and much more. With the integration of the CLR into SQL Server 2005, you can now write your own user-defined functions in C#.

To illustrate this, without getting bogged down in a complex calculation, the next example will create a C# user-defined function that takes a customer's first name, last name, and middle initial as parameters, and returns a string containing the appropriate full-name display.

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString NameDisplay(
    SqlString firstName,
    SqlString lastName,
    SqlString middleInitial)
{
    // we have access to the StringBuilder, a much better
    // concatenator than string addition
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("{0}, {1} {2}",
        lastName, firstName, middleInitial);
    return new SqlString(sb.ToString());
}
};