User Defined Functions of SQL with .NET CLR

Well, sometimes you might have thought if you had the power of .NET framework in your SQL query's, well here it is. You write your
1. Stored Procedure
2. User-Defined Function
3. Trigger
4. User-Defined Type
5. User-Defined Aggregate

all using C# or VB .net.

I'm going to blog here what i have done recently for my project. I required a webservice which will actually retreive some data using pattern matching.

For this i required to create a UDF that gives me easy regular exp checking using Regex in .NET,

It took me hardly 2 minutes, i opened Visual Studio, choose Visual C# and then Database and choose user defined function for SQL.


public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean RegExMatch(String failure_reason,String pattern)
{
Regex argstr = new Regex(pattern);
return argstr.Match(failure_reason).Success;
}
};

and thats it i compiled and deployed in SQL, went ahead and used the UDF as below

Select dbo.RegExMatch (..,..)

I am still completing this task but found this very useful thought this might com handy for you too sometime