.NET User Defined Functions in Sql Server

A bit slow on the wagon, but I didn't need this until now. It is all about creating a .NET assembly for use by the SQL Server. These are the quick and dirty steps to it.

C# Steps
1. Create a Class Library project (some links suggest an Sql Server Project, but that is not available for Visual Studio versions below Professional)
2. Add a public class
3. Add a public static method and decorate it with [SqlFunction]
4. Do not use static fields in the said class
Compile the assembly.

SQL Steps
1. Define the assembly in SQL:

CREATE ASSEMBLY MyAssembly FROM 'C:\SqlCLR\MyAssembly.dll'

2. Create the SQL function to use the method in the assembly:

CREATE FUNCTION MyUserDefinedFunction(
 @s1 NVARCHAR(4000),@s2 NVARCHAR(4000) ... other parameters )
RETURNS FLOAT AS
EXTERNAL NAME MyAssembly.[MyNamespace.MyClass].MyUserDefinedFunction

3. Enable CLR execution in SQL Server:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

4. use the function like

SELECT dbo.MyUserDefinedFunction('test','test2'...)

Things to remember:
1. Make sure the parameter types are the same in the .NET method and the SQL function
- the float keyword in SQL means double in .NET! I have no idea what kind of SQL type you must use in your function to match a .NET float.
- the string in .NET is matched to nvarchar in SQL
- the bit is matched to a bool as expected
2. Whenever you change the DLL you must DROP all the functions, then DROP the assembly, then create it again. If there are no signature changes, I guess just replacing the dll file could work.

There are a lot of things to say about returning tables instead of single values or about defining user defined aggregate functions, but not in this post.