Introduction

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. Creating the project 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 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:

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

Executing CLR Stored Procedure Using Server Explorer of Visual Studio

Using SQLServer Management Studio

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.