Step-by-step Create CLR Stored Procedure

Gaurav Kumar Arora
Mar 27, 2015
19k
0
2
Article

In this article we will create a CLR Stored Procedure in the following few steps:

Start Visual Studio 2010 or later.
Add a new project from File -> New -> Project.
Select Database Project (provide the name).
Create the project, it requires a Database Reference (I used EmployeeDB here).
In Solution Explorer right-click on the project name and click Add.
Select Stored Procedure.
Add a new file named "myTestStoredProcedure.cs".
The added file will look like:

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 myTestStoredProcedure()
{
//Right some code here
}
};

I am writing a code for the following three Stored Procedures:

myTestStoredProcedure: Simply prints a message.
spGetRolesList: Displays the rows from table.
spGetEmployeeList: Displays the rows from the table for a specific Age group.

The following code shows how to create a CLR stored procedure using Visual Studio:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
///
/// Prints a Message
///
[Microsoft.SqlServer.Server.SqlProcedure]
public static void myTestStoredProcedure()
{
//Simple proc
SqlPipe objSqlPipe = SqlContext.Pipe;
objSqlPipe.Send("Hi! I am simple CLR PROC");
}
///
/// Proc to Show Rows of [EmployeeDB]..[Roles] table
///
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetRolesList()
{
//It returns rows from Roles table
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @
"Select * from [dbo].[Roles] Order By HireDate";
conn.Open();
SqlDataReader sqldr = cmd.ExecuteReader();
SqlContext.Pipe.Send(sqldr);
sqldr.Close();
conn.Close();
}
///
/// It shows rows from Employee table on basis of supplied age
///
/// a specified age
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetEmployeeList(Int32 intAge)
{
//It returns rows from Employee table on basis of supplied age
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "Select * from [dbo].[Employees] Where Age >=@intAge Order By Age";
SqlParameter paramAge = new SqlParameter();
paramAge.Value = intAge;
paramAge.Direction = ParameterDirection.Input;
paramAge.DbType = DbType.Int32;
paramAge.ParameterName = "@intAge";
cmd.Parameters.Add(paramAge);
SqlDataReader sqldr = cmd.ExecuteReader();
SqlContext.Pipe.Send(sqldr);
sqldr.Close();
conn.Close();
}
};

Build and Deploy the assembly

Press Ctrl+Shift+B or select the Build option under Build.
If the project is successfully built, now it's time to deploy the assembly in SQLServer Directory.
Right-click on the project name in Solution Explorer.
Click on Deploy.
Check the Status Bar for further.
If It is deployed successfully, you can check the CLR procedure from Server Explorer.
Expand the EmployeeDB node.
Expand the Assembly node (you can find the "AStepAheadProcVisual" CLR Assembly).

Executing CLR Stored Procedure

Using Server Explorer of Visual Studio

Expand the AStepAheadProcVisual node.
Here you can see a class file and Assembly.info file including three procedures.
Right-click on any Stored Procedure (option availale: Open, Execute, Step Into Stored Procedure).
- Open: Directs to a specific procedure.
- Execute: Executes the selected procedure and result is available in Output window.
- Step into Stored Procedure: It debugs the application following the selected procedure.

Using SQLServer Management Studio

Open your SQLServer Management Studio (if not opened earlier)
Type in the following code in the Query Window.

Use [EmployeeDB]
Go
DECLARE @Role int
SET @Role = 28
Exec [dbo].[spGetEmployeeList] @Role
Go

The preceding code will display the EmployeeList with Employees having age 28 years or more.
In the same manner execute the other procedures.

Use [EmployeeDB]
Go
Exec [dbo].[myTestStoredProcedure]
Go
Exec [dbo].[spGetRolesList]
Go
DECLARE @Role int
SET @Role = 28
Exec [dbo].[spGetEmployeeList] @Role
Go

Scripts of Databases

The following are the scripts of the Table(s) used in the given example:

USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'EmployeeDB')
BEGIN
DROP DATABASE [EmployeeDB]
CREATE DATABASE [EmployeeDB]
END
USE [EmployeeDB]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[employees]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[employees]
CREATE TABLE [dbo].[employees](
[id] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[firstname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('First Name'),
[lastname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('Last Name'),
[age] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('19'),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[Roles]
CREATE TABLE [dbo].[Roles](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsRetiree] [bit] NOT NULL,
[HireDate] [datetime] NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Please note that:

If you get the error: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. When executing the preceding using SQLSERVER2005/2008, run the following code in Query analyzer: