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].
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5: using Microsoft.SqlServer.Server;
6:
7: namespace Demo.ClrUdf
8: {
9: public static class MyClrUdfClass
10: {
11: [SqlFunction]
12: public static string HelloWorld()
13: {
14: return "giddyup";
15: }
16: }
17: }
1: using System.Reflection;
2: using System.Runtime.CompilerServices;
3: using System.Runtime.InteropServices;
4:
5: // General Information about an assembly is controlled through the following
6: // set of attributes. Change these attribute values to modify the information
7: // associated with an assembly.
8: [assembly: AssemblyTitle("Demo.ClrUdf")]
9: [assembly: AssemblyDescription("")]
10: [assembly: AssemblyConfiguration("")]
11: [assembly: AssemblyCompany("Microsoft")]
12: [assembly: AssemblyProduct("Demo.ClrUdf")]
13: [assembly: AssemblyCopyright("Copyright © Microsoft 2011")]
14: [assembly: AssemblyTrademark("")]
15: [assembly: AssemblyCulture("")]
16:
17: // Used becuase we're not giving the assemby full trust in SQL
18: [assembly: AllowPartiallyTrustedCallers]
19:
20:
21: // Setting ComVisible to false makes the types in this assembly not visible
22: // to COM components. If you need to access a type in this assembly from
23: // COM, set the ComVisible attribute to true on that type.
24: [assembly: ComVisible(false)]
25:
26: // The following GUID is for the ID of the typelib if this project is exposed to COM
27: [assembly: Guid("63b93754-c707-4610-b1f4-c6e5949c0a99")]
28:
29: // Version information for an assembly consists of the following four values:
30: //
31: // Major Version
32: // Minor Version
33: // Build Number
34: // Revision
35: //
36: // You can specify all the values or you can default the Build and Revision Numbers
37: // by using the '*' as shown below:
38: // [assembly: AssemblyVersion("1.0.*")]
39: [assembly: AssemblyVersion("1.0.0.0")]
40: [assembly: AssemblyFileVersion("1.0.0.0")]
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.
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.
1: -- First, run this to enable the CLR. This only has to be done once.
2: sp_configure 'show advanced options', 1;
3: GO
4: RECONFIGURE;
5: GO
6: sp_configure 'clr enabled', 1;
7: GO
8: RECONFIGURE;
9: GO
10:
11:
12:
13:
6. Next, you must install / register your assembly with SQL Server. This is simple.
1: -- Run this, changing the names and paths appropriately (path is relative to the server)
2: -- Note the Safe permission set- this is best practice
3: -- Thus, you must set "[assembly: AllowPartiallyTrustedCallers]" in your AssemblyInfo.cs (and all referenced assemblies
4: -- Note that this will automatically register dependent / referenced assemblies
5:
6: USE [MyDatabase]
7:
8: CREATE ASSEMBLY DemoUserDefinedFunctions
9: FROM 'D:\Demo\Demo.ClrUdf.dll'
10: WITH PERMISSION_SET = SAFE
11:
12:
13: --DROP ASSEMBLY DemoUserDefinedFunctions
14:
15: --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().
1: -- Enable the CLR if it isn't already done
2: -- Register the assembly
3: -- Change the names and parameters of the UDF appropriatley
4: -- Input and output must match the method
5: -- Note the SQL to CLR mappings are obviously important- string only maps to unicode so we'll have to convert in our packages
6:
7: USE [MyDatabase]
8:
9: SET ANSI_NULLS ON
10: GO
11: SET QUOTED_IDENTIFIER ON
12: GO
13:
14: --CREATE FUNCTION EncryptUsernamePassword(@clearText nvarchar(255)) RETURNS nvarchar(255)
15: --AS EXTERNAL NAME XXXUserDefinedFunctions.[XXX.Database.Clr.UserDefinedFunctions.XXXCryptography].EncryptUsernamePassword
16: --GO
17:
18: CREATE FUNCTION HelloWorld() RETURNS nvarchar(255)
19: AS EXTERNAL NAME DemoUserDefinedFunctions.[Demo.ClrUdf.MyClrUdfClass].HelloWorld
20: GO
21:
22: -- DROP FUNCTION EncryptUsernamePassword
23:
8. You’re done. Just call your UDf or sproc from your T-SQL. EzPz.
Happy Coding,
Tom Hundley
Subscribe