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.

• 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

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...".


 
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.


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


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


 
Go to "Build" - "Deploy solution".


 
After successful deployment, we will get the Ok message as in the following. It indicates that the project was deployed in the specifiic 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.