Step-by-step Create CLR Stored Procedure

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  
  
   }  
};  

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.