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

Introduction

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.

Find more about SQL Server here: SQL Server

Before starting with the technical explanation, let me disclose the purpose of this series. In our current project, we have one requirement, "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 on 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 of SQL-CLR and all, but within a couple of hours, I realized that there are only a few relevant resources on 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 the following screens and it will get done.

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

CLR1.jpg

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

CLR2.jpg

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.

CLR3.jpg

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

CLR4.jpg

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

CLR5.jpg

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

CLR6.jpg

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

CLR7.jpg

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.

CLR8.jpg

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

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

CLR9.jpg

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

CLR10.jpg

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

CLR11.jpg

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

CLR12.jpg

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

CLR13.jpg

Conclusion

In the first article of the series SQL CLR For Beginners Part 1: Create Store Procedure in SQL-CLR Project. 

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

SQL CLR For Beginners: Part 2: Create Trigger in SQL-CLR Project            
SQL CLR For Beginners: Part 3: Create Function in SQL-CLR    
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


Similar Articles