Creating a SQL CLR User Defined Function

Starting with SQL Server 2005, Microsoft added the awesome ability to reference .Net assemblies from your T-SQL procedures.  Here’s a quick 5 step overview on how to get up and running with your code-based User Defined Function.

1) Create a project from the Visual C# SQL CLR Database Project template (just so you can get the correct references and get a test harness going).

2) Add a class containing your UDFs. This one does a basic Regular Expression comparison:

public class MyUdf
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlBoolean FnRegExMatch(string source, string pattern)
{
Match m = Regex.Match(source, pattern,
RegexOptions.CultureInvariant |
RegexOptions.IgnoreCase |
RegexOptions.Singleline
);
return m.Success;
}
}

3) Enable the CLR on your SQL Server instance through the configuration options:

EXEC sp_configure 'show advanced options' , '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options' , '0';
GO

4) Add the assembly and then create a reference to your UDF:

CREATE ASSEMBLY [MyUdfs]
AUTHORIZATION [dbo]
FROM 'C:\SqlServerClr\MyUdf.dll'
WITH PERMISSION_SET = SAFE -- we only have "safe" calls in our lib
GO
 
CREATE FUNCTION [dbo].[FnRegExMatch](
@SOURCE nvarchar(4000),
@pattern nvarchar(4000)
)
RETURNS BIT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyUdf].[FnRegExMatch]
GO

5) Now you should be able to call your function:

SELECT dbo.FnRegExMatch('my test string', '\btest\b')

Of course this is just the barest of essentials to get you going with SQL CLR UDFs, but I hope it helps!

 

This entry was posted in C#, MS SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

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