Introduction
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).
- Creating the project 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 this:
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 a 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, 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.
- You can check if the CLR procedure from Server Explorer is deployed successfully.
- 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 an Assembly.info file, including three procedures.
- Right-click on any Stored Procedure (option available: Open, Execute, Step Into Stored Procedure).
- Open: Directs to a specific procedure.
- Execute: Executes the selected procedure, and the result is available in the 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 aged 28 years or older.
- 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 an 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
Conclusion
This article taught us how to create a CLR-stored procedure step by step with code examples.