SQL Server integration with CLR aka SQL CLR (Part II – Creating a stored procedure in SQL CLR)

Introduction and background

Now,we have know-how of what SQL CLR is and its benefits and when to choose it. In this write-up we would learn to create a stored procedure, deploy and test the same.

Objective

To learn how to create a stored procedure in SQL CLR and use it. 

Description

The tools for development are VS 2010 and SQL server 2008 R2. The database “AdventureWorks” is referred. The first step in learning the SQL CLR database items is to create a SQL CLR project.

Visual Studio provides a template for creating such project which is named as SQL CLR Database project. Please create a project as shown above and name it as “SqlServerProject”. The snapshot for project organization is as below.

The folder named “Test Script” contains the file “Test.sql” where in the test script can be included.

Now, we are targeting to create a store procedure. The step is same as creating any new item and select the option for “Stored Procedure”. Have a look at other options like aggregates, triggers etc

Let’s name the class as “StoredProcedures”. We are going to have SELECT queries in envisaged stored procedure. As SELECT queries are used, we need to use “SqlDataReader” and also SqlContext pipe to send the results. Following code snippet demonstrates the code for creating a stored procedure with SELECT queries.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
/// <summary>
/// Following code creates a stored procedure named SQLCLRStoredProcedure_CS in the Adventure Works sample database. 
/// This stored procedure contains SELECT queries
/// </summary>
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SQLCLRStoredProcedure_CS()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand SQLCLRStoredProcedureCommand = new SqlCommand();
SQLCLRStoredProcedureCommand.CommandText = "SELECT TOP 2 * from Production.Product;" +
"SELECT TOP 2 * from Sales.Customer;";
SQLCLRStoredProcedureCommand.Connection = conn;
conn.Open();
SqlDataReader sqlDataReader = SQLCLRStoredProcedureCommand.ExecuteReader();
SqlContext.Pipe.Send(sqlDataReader);
conn.Close();
}
}
};

Yes, we have created our first stored procedure. As VS 2010 provided facility of building, deploying and running the test script in one go, we can follow this route. VS 2010 also allows building and deploying separately. Refer to following figure

The another way to accomplish this is --> 1. Build 2. Deploy in SQL Server with T SQL.

For deployment through T SQL, following statements needs to be executed in SQL query window (complete path needs to be provided for the assembly)

CREATE ASSEMBLY SqlServerProject
FROM '…… \SqlServerProject\bin\Debug\SqlServerProject.dll'
WITH PERMISSION_SET = SAFE;
CREATE PROCEDURE [dbo].[SQLCLRStoredProcedure_CS]
AS
EXTERNAL NAME [SqlServerProject].[StoredProcedures].[SQLCLRStoredProcedure_CS]
GO

We are done with creating a stored procedure in SQL CLR and its deployment. Hurray, definitely it is easy than we expected. Isn’t it?

When we browse the stored procedure in management studio, we get to see following

Isn't it surprising that no T SQL code is seen? As the stored procedure is secured and hence not editable in management studio. This is not going to make database developers/ administrators happy but has it is part of assembly; this is the way it should behave.

Now we will have to test it. Wait, one most important step is still not performed and what is it? Yes, the SQL server has a configurable option for enabling CLR without which the database items created through SQL CLR won’t execute. Let’s see how to do it.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Testing

For testing through VS 2010, please add following code in “Test.sql”.

When one chooses to build, deploy and test through VS 2010, the output of testing is displayed in output window as “Database output”.

The same script i.e. EXEC .... can be used in SQL Query window in management studio for SQL server and we can confirm the results.

Summary and Conclusion

Hope this helps to help to create SQL CLR stored procedure and start exploring the SQL CLR world.

HAPPY PROGRAMMING!!!

Reference

Please see this link