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

Sourav Kayal
11y
54.9k
0
0

Article

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