In the previous article ,http://www.dotnetfunda.com/articles/article1727-sql-server-integration-with-clr-aka-sql-clr-part-i-introduction-and-back.aspx we discussed what is SQLCLR, why SQLCLR and the steps for employing SQL CLR into our work. This article discusses the steps for creating a stored procedure, deployment and testing the same. This is second write-up of multipart series on this subject.
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.
To learn how to create a stored procedure in SQL CLR and use it.
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.
/// Following code creates a stored procedure named SQLCLRStoredProcedure_CS in the Adventure Works sample database.
/// This stored procedure contains SELECT queries
public partial class StoredProcedures
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);
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]
EXTERNAL NAME [SqlServerProject].[StoredProcedures].[SQLCLRStoredProcedure_CS]
These statements create an assembly in SQL server and also create a stored procedure referring the assembly just created.
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
For testing through VS 2010, please add following code in “
When one chooses to build, deploy and test through VS 2010, the output of testing is displayed in output window as “
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 ConclusionWe are through our first exercise of creating a stored procedure in SQL CLR along-with deployment and testing. In retrospection, it seems easy to implement SQL CLR contrary to the situation before one make his/ her hands dirty, the task seems daunting.
Hope this helps to help to create SQL CLR stored procedure and start exploring the SQL CLR world.
Please see this link