SQL Server integration with CLR aka SQL CLR (Part III – Creating a SQL server User Defined Function i.e. UDF in SQL CLR)

Introduction and background

In the first article at , we discussed what is SQLCLR, why SQLCLR and the steps for employing SQL CLR into our work. The next article , we explored how to create a stored procedure. In this article, we would see the creation of a SQL server UDF in SQL CLR.

Hardly there would be any programmer who doesn’t know what is a function in programming language. A function is a subroutine which includes frequently performed logic. This is cornerstone for modular programming and helps to avoid the repetition of logic and promotes reuse.

In the release of SQL server 2000, functions were introduced. These functions are categorized as built in and user defined. Built in functions can’t be modified by user and UDF allows user to create their own functions. These UDF greatly enhance the reuse across database items as these can be referenced in stored procedures, inline SQL in front end languages etc.

Objective

To learn how to create a UDF in SQL CLR and use it. 

Description

Note: The tool for development is VS 2010 and SQL server 2008 R2. The database “AdventureWorks” is referred. We have seen creation of SQL server database project and we would move on to creating the UDF.

The database “AdventureWorks” has table [AdventureWorks].[Purchasing].[PurchaseOrderHeader] in which the freight charges needs to be calculated. We would write a UDF in SQL CLR project for calculating these fright charges.

We would add the UDF in the same project wherein we created a stored procedure. A UDF can be added to the project as shown below.

The template is provided by VS 2010 for database items. Please select the template for UDF.

This function helps to calculate freight on the amount of subtotal and returns the total fright. The code snippet is as follows

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
    public const double FREIGHT_CHARGES = .025;//Freight charges
    /// <summary>
    /// Returns the freight charges calculated on the subTotal for purchas order
    /// </summary>
    /// <param name="subTotal"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble AddFreight(SqlDouble subTotal)
    {
        SqlDouble freightCharges = subTotal * FREIGHT_CHARGES;
 
        return freightCharges;       
    }
};

Please note that in a function the SQL data type needs to be used. We are now ready with our first SQL CLR UDF and next logical steps are to build and deploy before we test our function. We know VS 2010 facilitates building and deploying and also provides the database output. To confirm the result of UDF in database output, we need to add script in “Test.sql” file as follows

SELECT dbo.AddFreight(10);

When the UDF is built and deployed, following output can be seen in VS 2010.

Let’s have a look at the UDF we created and deployed

This function is grouped under the scalar valued functions under the Programmability --> Functions group.

How about testing the function in T SQL in SQL query? Following is the result of such testing.

So straightforward, right? Creating, building, deploying and running (testing) a SQLCLR UDF is not a big deal and with VS 2010 support for build and deploy and also provision for test output in one go, is great booster. This eases a lot of mundane tasks of doing these manually and one after other.

Summary and Conclusion

We are through our first exercise of creating a UDF in SQL CLR along-with deployment and testing. With Visual Studio support, creating database items for SQL CLR has become an easy task and this should come as good news for programmer fraternity.

Hope this helps to help understand how to create SQL CLR UDF and start exploring the SQL CLR world.

HAPPY PROGRAMMING!!!

Reference

Please see this link.