Creating and Enabling a CLR Function.

As a very simple example of CLR integration I create a CLR Assembly function to be called from SQL here.

Prerequisite to this exercise > Enabling CLR Integration on SQL server.

Step 1 : Create a basic class in .NET -

• Start Visual Studio 2005
• File , New , Project
• Select 'Visual Basic' (left hand navigation pane) and 'Class Library' (right hand navigation pane).
• Select OK.

You are presented with a blank class template , which looks like this -


Way too much .net explanation would have to go here, but basic steps are >

• Name your class
• Name and code a function to be called from SQL (i've chosen a Roman Numerals one here).
• Save it!
• Give it a more sensible name -
  Rename ClassLibrary1 to SQL_CLR_Project
  (Do this in Solution Explorer on the right hand side)
• File , Save All

Step 2 : Compile the class to a .DLL file >

Change Project properties -

• Project menu , SQL_CLR_Project Properties
• Change Assembly Name - I chose 'CLRFunctionLibrary' (This will match the .dll name i.e CLRFunctionLibrary.dll will be generated).
• Change the Root Name Space. I changed this to 'SQLCLROne' to match the project.

Compile it -

• Build Menu - 'Build SQLCLROne'

Find the dll just created (CLRFunctionLibrary.dll). -

C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\obj\Debug


Step 3 : Register the assembly within SQL Server -

In TSQL -

CREATE ASSEMBLY [CLRFunctionLibrary]
FROM 'C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\bin\Debug\CLRFunctionLibrary.dll'
WITH PERMISSION_SET = SAFE
GO

These 2 queries verify the Assembly has been created, and will show other assemblies present -

SELECT * FROM sys.assemblies

SELECT * FROM sys.assembly_files

Step 4: Register the method within the assembly. -

CREATE FUNCTION functionName
 (@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName

CREATE FUNCTION dbo.fn_RomanNumeral
 (@number AS INTEGER)
RETURNS NVARCHAR(20)
AS EXTERNAL NAME CLRFunctionLibrary.[SQLCLROne.SQLCLROne].RomanNumeral

Notes :

1. I had to declare the Namespace when calling from the Assembly i.e the declaration became >
CREATE FUNCTION functionName
(@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName


2. 2 Use the type of NVARCHAR not VARCHAR to match with the output STRING declared in vb.net.

Now the function is created, you can see it like this -

SELECT * FROM sys.assembly_modules

and test it like this -

select dbo.fn_RomanNumeral(1066)
select dbo.fn_RomanNumeral(1976)
select dbo.fn_RomanNumeral(2007)

If you experience compatibility issues you can determine which version of .NET is installed by querying the sys.dm_os_loaded_modules DMV (dynamic management view).

SELECT *
FROM sys.dm_os_loaded_modules
WHERE [name] LIKE N'%\MSCOREE.DLL'