SQL CLR For Beginners: Part 3: Create Function in SQL-CLR

Introduction

Welcome to the SQL-CLR For Beginners article series. In this article series, we discuss working with the SQL Server database component using C#. In the previous two articles, we saw how to create a simple Stored Procedure and triggers. If you are unfamiliar with them, please visit the following links.

• SQL CLR For Beginner: Part 1: Create Store Procedure in SQL-CLR Project
• SQL CLR For Beginner: Part 2: Create Trigger in SQL-CLR Project

This article will show how to create a simple function in a SQL-CLR Project. As we know, a function is a SQL Server (and in other databases, too) component. We can use them for calculation purposes or to execute a repeated job. In a few steps, let's see how to create a SQL Server function using the C# language.

Let's open Visual Studio 2010. Go to "File" -> "New" -> "Project...".

The "SQL Server" templates under the Database node are in the left-hand panel. Choose the "Visual C# SQL CLR Database Project" template. Provide your favorite name and select a location.

Once you click on the "Ok" button, it will pop up the following window and ask you to select a SQL Server database. You need to choose the database for this project.

Click the "Add New Reference" button, and a will pop up a window to configure your database connection. Select your server name and database name. After selection, ensure a connection can be established by pressing the "Test Connection" button.

Once you press "OK," the sample project structure will be created in the Solution Explorer section as in the following.

Right-click on "Test Script" - "Add" - "New Item."

Choose "User Defined Function" and provide a proper name. I used "MyFun" in this demonstration.

Once you click "Add," it will create a sample function as in the following.

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 MyFun()        {            // Function Body goes here            return new SqlString("This is my First Function");
       }    };

The body of the C# function is nothing but a SQL Server function. Suppose we look closely at "MyFun()." Then we will see an attribute above the MyFun() function. It indicates to the compiler that the following function is a SQL type function and to take special care at compile time.

Now we need to change the .NET Framework version. By default, the target framework version is 4.0; we need to make it 3.5 because SQL Server supports version 3.5 of the framework.

Go to "Build" - "Deploy solution."

After successful deployment, we will get the OK message as follows. It indicates that the project was deployed in the specific database.

Now open SSMS.

Write and execute the following code to run the function. And you will find the output in the output section of SSMS.

Conclusion

In this series article, we create a simple function in a SQL-CLR Project with the help of stored procedures and triggers in an SQL Server.

For reading the next articles of this series, Please go through these links- 

• SQL-CLR For Beginners: Part-4: Consume C# Class in Stored Procedure
• SQL-CLR For Beginners: Part-5: Call Function and Procedure From Other Procedure