Use Expression in CLR

Expression class under System.Linq.Expressions is a very useful class which includes many static members helping you to create expression tree and execute the tree. Whenever you need to execute a dynamically generated logic in SQL Server through an CLR, you might consider using the classes in Expressions name space. In this post, I am going to give you an very simple example to explain how to create and execute an expression tree.

An CLR function with 2 parameters will be created. The logic is below

 return case when a<b then 1 else 0 end

Now I want to implement it through CLR using Expressions name space.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean ExpressionTreeTest(SqlInt32 a, SqlInt32 b)
{
    ParameterExpression p1 = Expression.Parameter(typeof(int)); //parameter a
    ParameterExpression p2 = Expression.Parameter(typeof(int));//parameter b

    BinaryExpression p3 = Expression.LessThan(p1, p2); // create expression tree
    //compile expression tree
    Expression<Func<int, int, bool>> le = Expression.Lambda<Func<int, int, bool>>(p3, p1, p2); 
    Func<int, int, bool> fn = le.Compile();
    //execute tree and return.
    return new SqlBoolean(fn(a.Value, b.Value));
}

Code above is very straight forward. Line 4 and 5 define the parameters of the expression. Line 7 defines a binary expression tree using less than operator. Now the tree is saved in variable p3. Line 9 and Line 10 compile the tree. Line 12 execute the delegation and return the value.

select dbo.ExpressionTreeTest(1,2)
select dbo.ExpressionTreeTest(2,1)
/*

-----
1

(1 row(s) affected)


-----
0

(1 row(s) affected)


*/