Step-by-step Create CLR Stored Procedure

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

1. Start Visual Studio 2010 or later.
2. Add a new project from File -> New -> Project.
3. Select Database Project (provide the name).
4. Create the project, it requires a Database Reference (I used EmployeeDB here).
5. In Solution Explorer right-click on the project name and click Add.
6. Select Stored Procedure.
7. Add a new file named "myTestStoredProcedure.cs".
8. 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:

sp_configure 'clr enabled', 1
go
reconfigure
go

• You can also Debug the produced code within Visual Studio by starting debugging from the DEBUG MENU.