Archive for category: Blog

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.