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

Welcome to the SQL-CLR For Beginners article series. In this article series we are discussing how to work 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 not familiar with them then please visit the following links.

In this article we will see 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 basically for calculation purposes or to execute a repeated job. Let's see how to create a SQL Server function using the C# language in a few steps.
 
Let's open Visual Studio 2010. Go to "File" -> "New" -> "Project...".

image1.gif
 
In the left hand panel, you will find the "SQL Server" templates under the Database node. Choose "Visual C# SQL CLR Database Project" template. Provide your favorite name and select a location.

image2.gif
 
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.

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

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

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

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

image7.gif
 
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. If 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.

image8.gif
 
Go to "Build" - "Deploy solution".

image9.gif
 
After successful deployment, we will get the Ok message as in the following. It indicates that the project was deployed in the specifiic database.

image10.gif
 
Now open SSMS.

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

image12.gif