Transcend T-SQL Limitations with SQL Server 2005 CLR Objects
One of the coolest SQL Server 2005 feature is .NET CLR objects. When use wisely, CLR integration can solve many nagging problems with T-SQL. For example, you cannot pass columns from an outer SQL statement to a TBF even though it returns one row. Or, for some obscure reason, you cannot use dynamic execution (EXEC statement) inside a scalar –valued function. Yet, you may need to use the same scalar-valued function with an arbitrary column.
In comparison, the sky is the limit about what a CLR stored procedure or a CLR UDF can do. Here is an extract from a real-life CLR UDF written in C# that returns the YTD aggregated value for a given measure:
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlDecimal YTD(int companyID, DateTime financialPeriod, string measure) {
using (SqlConnection conn = new SqlConnection("context connection=true")) {
conn.Open();
SqlCommand cmd = new SqlCommand(String.Format("SELECT SUM({0}) " +
"FROM <some table here> (NOLOCK) " +
"INNER JOIN <some other table> (NOLOCK) " + "… " +
"WHERE FinancialPeriod = @FinancialPeriod" ", measure), conn);
cmd.Parameters.Add(new SqlParameter("@CompanyID", companyID));
cmd.Parameters.Add(new SqlParameter("@FinancialPeriod", financialPeriod));
return ToSqlDecimal(cmd.ExecuteScalar()); }
}
private static SqlDecimal ToSqlDecimal(object value) {
return value is System.DBNull ? SqlDecimal.Null : SqlDecimal.Parse(value.ToString());
}
Here, the function takes a company identifier, financial period, and the name of the measure to be aggregated as input arguments. The ADO.NET SqlCommand object takes care of executing the query (a boiler-plate ADO.NET code). Note the SystemDataAccess = SystemDataAccessKind.Read attribute that decorates the function. If you omit it, you will be greated with the following exception at runtime:
This statement has attempted to access data whose access is restricted by the assembly.
Once deployed, the function can be called as a regular T-SQL UDF, e.g.
SELECT <FULLY QUALIFIED CLASS NAME>.YTD(1, '7/1/2006', "Sales"),
assuming you have a Sales decimal column in your table or view.
Who knows, perhaps one day, we will be able to ditch T-SQL whatsoever in favor of .NET languages. I know I will be the first one to jump.
