Creating A CLR Function (SQL SERVER 2005)

[NeedSomeAnswers]

Title - Creating a CLR Function – (Converting Numbers to Words)

Description

CLR or Common Language Runtime functions allow you as a developer to harness the power of .net inside SQL Server, and in the case of functions, actually in-line with your SQL.
This means you can do things like expose .Nets regular expressions and string manipulation ability in your function.
In this example I will take a C# Class that I have found on the internet, which converts Numbers to their Word representation, and alter it so that it fits inside the CLR Function, I will then show how to register and run the function within SQL Server.

Requirements

SQL SERVER 2005 & VS Studio 2005 or above


Tutorial

First you need to create a new project inside visual studio. Go to New | Project >> Database | SQL Server Project.

Fig1.




Once you have named & created your project right click on the project and select Add | User-Defined Function.

Fig 2.



This will add a class with a basic function shell to the project for you, and it will look something like this

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Function1()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Above the function declaration you will notice this line -

[Microsoft.SqlServer.Server.SqlFunction]

If you wish to read from the SQL Server tables inside your CLR function you will need to alter it to read;

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

Now remove the current function outline so you have a blank class awaiting the import of code.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    
//Code Goes Here
 
};

At this point you could essentially add in any supported code into your project that takes in a value and returns something.
I was looking for some specific code to convert Numbers to Words, something that is very useful in Cheque processing for instance. I decided to use the code from the following site -

http://www.codeproject.com/KB/cs/codesamples.aspx

You will then need to import the code, and changed all the subs/functions to static, as they have to be static in a CLR Function and because logically they should be static anyway as a CLR Function is not object specific and returns a value based upon the Input, not upon an object.
e.g.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction] (DataAccess = DataAccessKind.Read)]
    
public static SqlString convertNumericToWords(SqlDouble numb)
        {
            String num = numb.ToString();
            return (changeToWords(num, false));
        }
};

For the Public functions that are going to be registered with SQL Server you will also need to change the data types of the function itself and its input variables. The variables need to match up by Type with what it is being passed by SQL Server.

There is a great link which tells you what datatypes map to which – Here

The Datatypes SqlString & SqlDouble along with others all become available because you are in a .Net SQL Server Project, and their SQL Server equivalents are nVarchar and Float.