Create a SQL CLR Stored Procedure Using .NET (C# Example)

T-SQL is limited in its functions and getting the results you need sometimes becomes very complex and the statements used consequently hard to decipher. With SQL Server 2005, Microsoft introduced CLR (Common Language Runtime) technology which allows you to use .NET framework to create stored procedures, among other things, in .NET languages and invoke them on the SQL Server using T-SQL. This means you can use any .NET language to write them and yet call them using T-SQL syntax.

A very common problem with T-SQL language is that it does not support regular expressions. And a very common thing you want to do is to search for records using regular expressions. So, let’s implement an assembly that does just that, register it with the SQL Server and create a regex function.

So, how do you start? – First, create a new Class Library project in your Visual Studio, I’ll call it MyRegex for our example, and then create an empty class called Regex. The Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces provide you with attribute and data classes you will need to implement the CLR function. So, let’s look at it:

using System.Collections;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

namespace MyRegex
{
	public sealed class Regex
	{
		[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true, Name = "myRegexMatch", SystemDataAccess = SystemDataAccessKind.None)]
		public static SqlBoolean RegexMatch(SqlString input, SqlString pattern, SqlBoolean ignoreCase)
		{
			return input.IsNull || pattern.IsNull ? SqlBoolean.Null : new SqlBoolean(Regex.IsMatch(input.Value, pattern.Value, ignoreCase.Value ? RegexOptions.IgnoreCase : RegexOptions.None));
		}
	}
}

The SqlFunction attribute on the method is what is telling the compiler and the SQL Server that it is a CLR/SQL function. As always, you want to check your NULLs, and then return the result based on your input parameters. The parameters and the result, of course, need to be defined as CLI types.

The next step is to compile your solution and import it into the SQL Server. Once you compiled your code and have MyRegex.dll, copy it over to a local directory on your SQL Server – for this example, let’s assume it’s in c:\regex directory. Make sure you enable CLR on your SQL Server if you have not already done so.

Open SQL Management Studio and import the assemly:

CREATE ASSEMBLY MyRegex FROM 'c:\regex\MyRegex.dll' WITH PERMISSION_SET = SAFE

This command will store your DLL as a binary stream on the server and let you reference it with MyRegex name. Now, all that’s left to do is to define a function that will use you CLR code:

CREATE FUNCTION dbo.funcRegexMatch (@input VARCHAR(MAX), @pattern VARCHAR(MAX), @ignoreCase BIT)
	RETURNS BIT
	AS EXTERNAL NAME MyRegex.[MyRegex.Regex].RegexMatch

Note that the first part, MyRegex, is the name you gave in the CREATE ASSEMBLY statement, followed by your full C# class name including the namespace and, finally, followed by the actual CLR method name.

Congratulations! You can now execute a query like this:

SELECT *
	FROM People
	WHERE
		funcRegexMatch(Phone, '^[0-9]{10}$', 0)
This entry was posted in C# and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *