Step-by-step Create CLR Stored Procedure

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:

  • 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

  • If you get the error: Execution of user code in the .NET Framework is disabled. Enable the "clr enabled" configuration option. When executing the preceding using SQLSERVER2005/2008, run the following code in the 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.

Conclusion

This article taught us how to create a CLR-stored procedure step by step with code examples. 


Similar Articles