Programming, SQL Database, Tips & Tricks

Use CLR functions in SQL

In SQL database you can create functions and stored procedures for calculating data and return some values but as the limitation of SQL as it is not full programming language so sometimes you can not do your stuff with SQL only sometimes you have to go to CLR function to transform some data or calculate some values. CLR functions also useful when you have to call web service with the dynamic value from the database and get return value and do next step.

If you are new to know about CLR function then you can see below example that how I can call the C# function in SQL easily and it is just an example but you can extend the level of CLR function even you can create aggregate functions easily.

1. Create Class Library project with name “CLRDemo” to build your DLL with CLR functions.

Class library project

2. Create new class with name CLRFunction

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CLRDemo
    public class CLRFunctions
        public const double SALESTAX = .099;

        public static SqlDouble CalculateTax(SqlDouble Amount)
            SqlDouble taxAmount = Amount * SALESTAX;

            return Amount + taxAmount;

Here you have to add

[SqlFunction()] attribute for define SQL function and also you must use SqlTypes to return.
CalculateTax is a simple function to calculate tax amount based on passed value from the database. Build this project and generate dll which name will be CLRDemo.dll.

3. Enable CLR in SQL Server which is disabled by default. You can run below script to enable the CLR to register and call dll functions.

sp_configure 'show advanced options', 1
sp_configure 'clr enabled', 1
sp_configure 'show advanced options', 0

4. Register CLRDemo.dll to SQLSever so SQL know about your function is available to use.

Create Assembly CLRDemo from 'c:CLRDemo.dll' with Permission_set = SAFE

5. Use CLR function in SQL

SELECT dbo.CalculateTax(452)

you can use CLR function same as the sql function to use the name that you defined in CLR function and pass the parameter value and get the result back with same SQL type.
You can easily call functions that SQL has some limitation to do like some data transformation and also c# provide object oriented programming to gives you polymorphism and inheritance which allow for cleaner more organized code. It is also more secure alternative rather use the stored procedure which has some limitation. There is one disadvantage to re-register dll when you have made changes in the class file and to use that change in the sql side. The main benefit that you can get the best performance while you have to call mathematical calculation in each raw and sometimes you don’t know how to implement in SQL then it is easy to use CLR where your native language C# or VB.Net can do easily.


2 thoughts on “Use CLR functions in SQL

Leave a Reply

Your email address will not be published. Required fields are marked *