SQL CLR For Beginners: Part 1: Create Store Procedure in SQL-CLR Project

Welcome to the SQL-CLR For Beginners article series. This is the first presentation in this series. In this article series, we will understand a few important concepts to develop projects in SQL Server CLR. Please don't be confused by the name SQL-CLR, It's nothing but a SQL Server CLR project. For the sake of simplicity, in this article we will call it SQL-CLR.

Before starting with the technical explanation, let me disclose the purpose of this series. In our current project we have one requirement, that "We need to consume a service from a database layer component (in other words from a Stored Procedure or Trigger)". And by God's grace (because I am pretty new in this area ) I am handling this work alone. I began to search in the web and asked a few people to provide suggestions and ideas. (They may be very busy and most of them did not reply, except Jean Paul Sir. Yes, I wasn't too shy to say publicly "I have received help".) OK, somehow I was able to understand that the SQL CLR project is the best solution to deal with such problems. I started to learn the concepts SQL-CLR and all, but within a couple of hours, I realized that there are only a few relevant resources in the web.

And I decided that OK, let's start a series with the same topic.

How to create a Stored Procedure using SQL-CLR?

In this article we will see, how to create a simple Stored Procedure using C# code and how to deploy it in SQL Server. Just follow following screens and it will get done.

Open Visual Studion 2010 then select "File" -> "New" -> "Project...".



Select the Database node in the left panel and select "Visual C# SQL CLR Database Project". Give a suitable name of your project. In my case I used the name "MyCLR" and selected a location to save it to.

Once you press OK, it will prompt you to choose a database server. You may choose your local server or remote server. I have chosen my local server. Click on the "Add New Reference" button.

I gave my local server name and database name. Make a test connection by pressing the "Test Connection" button.

It will now open one new SQL-CLR Project and if you look at the Solution Explorer then you will find the following structure:

Here we will add a Stored Procedure. Right-click on the Test Script folder then select "Add" -> "Stored Procedure".

Select Stored Procedure and provide your favorite name. I gave "MySP".



Put the following code in your Stored Procedure. It will print "Hello world" when we execute the Stored Procedure.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; 

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MySP()
    {
        SqlPipe sqlP = SqlContext.Pipe;
        sqlP.Send("Hello World");
        // Put your code here
    }
};

Here we will change the .Net Framework version of this project from 4.0 to 3.5 because SQL Server 2008 does not support 4.0.

Right-click on the project  then select "Properties" -> "Application" -> "Target Framework" then select 3.5.



This change will take effect after restarting the project .So close it and re-open it.

Go to "Build" -> "Build Solution".



Again go to "Build" ->"Deploy Solution".

If everything is fine then it will say that the deployment was successful.

Now we will run the Stored Procedure from SQL Server. So, Open SSMS.

Write exec.dbo.MySP and run. I hope you see output as in the following screen.