Implementing CLR User Defined Function (UDF) in SQL 2008 R2

Implementing a CLR User Defined Function (UDF) in SQL 2008 R2 is very easy. Note that this code is not limited to 2008 R2 or UDFs, and implementing CLR stored procedures is just as simple.

The steps are as follows:

1)  Create you class with all desired methods / functionality. Note that your classes and methods should be static!

2)  Add appropriate attributes to the methods you want to utilize as CLR objects. For a UDF, you add the [SqlFunction] attribute. For a CLR stored procedure, you would use [SqlProcedure]. Note that these attributes are in the Microsoft.SqlServer.Server namespace.

3)  It’s considered best practice to install register your assembly with SQL Server using Partial Trust. To that end, you must Allow Partially Trusted Callers in your assembly’s AssemblyInfo class. Do this my simply adding [assembly: AllowPartiallyTrustedCallers].

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Demo.ClrUdf
{
    public static class MyClrUdfClass
    {
        [SqlFunction]
        public static string HelloWorld()
        {
            return "giddyup";
        }
    }
}
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;

// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("Demo.ClrUdf")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Microsoft")]
[assembly: AssemblyProduct("Demo.ClrUdf")]
[assembly: AssemblyCopyright("Copyright © Microsoft 2011")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

// Used becuase we're not giving the assemby full trust in SQL
[assembly: AllowPartiallyTrustedCallers]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("63b93754-c707-4610-b1f4-c6e5949c0a99")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Build and Revision Numbers
// by using the '*' as shown below:
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

clr1

4)  Sign your assembly.  The easiest way to do this is to use a new string key name file by right clicking on your project, click on the Signing Tab, and click Sign the assembly.  This will create a PFX certificate in your project folder that is used to create the signature.

clr2

5)  You’re done with the assembly.  Now on to SQL Server.  First, you need to make sure that the CLR is enabled on the server.

-- First, run this to enable the CLR.  This only has to be done once.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

6)  Next, you must install / register your assembly with SQL Server. This is simple.

 -- Run this, changing the names and paths appropriately (path is relative to the server)
-- Note the Safe permission set- this is best practice
-- Thus, you must set "[assembly: AllowPartiallyTrustedCallers]" in your AssemblyInfo.cs (and all referenced assemblies
-- Note that this will automatically register dependent / referenced assemblies

USE [MyDatabase]

CREATE ASSEMBLY DemoUserDefinedFunctions
FROM 'D:\Demo\Demo.ClrUdf.dll'
WITH PERMISSION_SET = SAFE


--DROP ASSEMBLY DemoUserDefinedFunctions

--SELECT * FROM sys.assemblies

7)  Finally, Create the CLR UDF or CLR Stored Procedure. Here’s an example of creating a UDF with the assembly we just registered. I used a simple Demo class for this blog, but I left one my of implementation of a real UDF in this sample so you can see how to pass parameters, etc. Note, the only thing that might trick you up a big is the return type of the methods. For strings, it has to support Unicode, so use nvarchar().

 -- Enable the CLR if it isn't already done
-- Register the assembly
-- Change the names and parameters of the UDF appropriatley
-- Input and output must match the method
-- Note the SQL to CLR mappings are obviously important- string only maps to unicode so we'll have to convert in our packages

USE [MyDatabase]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--CREATE FUNCTION EncryptUsernamePassword(@clearText nvarchar(255)) RETURNS nvarchar(255) 
--AS EXTERNAL NAME XXXUserDefinedFunctions.[XXX.Database.Clr.UserDefinedFunctions.XXXCryptography].EncryptUsernamePassword
--GO

CREATE FUNCTION HelloWorld() RETURNS nvarchar(255) 
AS EXTERNAL NAME DemoUserDefinedFunctions.[Demo.ClrUdf.MyClrUdfClass].HelloWorld
GO

-- DROP FUNCTION EncryptUsernamePassword

8)  You’re done. Just call your UDf or sproc from your T-SQL. EzPz.

Happy Coding,

Tom Hundley

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s