Execute C# Methods from SQL Server

Recently I had a requirement in which I need to call a .NET library class method from SQL Server Stored Procedure and Microsoft SQL Server does provide that feature. Basically It was kind of string manipulation and we also used for calculating the numbers for which the algorithm was defined in .Net function. For example in our case we need to get the amount with GST included.

​Creating the Class Library
First step is create the .Net class library and we have to make sure the target framework supports the Microsoft SQL Server feature of calling the CLR methods. I have created a class library targeting .net framework 4.5 and will be using the class library in SQL Server 2014.

​First thing, we need to ensure that the method we need to call from SQL Server is marked as static and also we need to decorate the function with Microsoft.SqlServer.Server.SqlProcedure attribute. Below is my code which I am using.

​Register the Class Library in SQL Server
Once the class library is finalized and ready to execute from SQL Server, you need to execute some basic SQL script before utilizing it.

Register Class Library in SQL Server

Once the script has finished executing, you will find the registered assembly under the Database\Programmability\Assemblies node in SQL Server Management Studio. If you look under “Database\Programmability\Functions”, you'll find the method you registered earlier. You can now call your method as a standard T-SQL function call.