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:
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Data.SqlTypes;  
  5. using Microsoft.SqlServer.Server;  
  6.   
  7. public partial class StoredProcedures  
  8. {  
  9.   
  10.    [Microsoft.SqlServer.Server.SqlProcedure]  
  11.    public static void myTestStoredProcedure()  
  12.    {  
  13.       //Right some code here  
  14.   
  15.    }  
  16. };  
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:
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Data.SqlTypes;  
  5. using Microsoft.SqlServer.Server;  
  6.   
  7.   
  8. public partial class StoredProcedures   
  9. {  
  10.     ///    
  11.     /// Prints a Message    
  12.     ///    
  13.     [Microsoft.SqlServer.Server.SqlProcedure]  
  14.     public static void myTestStoredProcedure()   
  15.     {  
  16.         //Simple proc    
  17.         SqlPipe objSqlPipe = SqlContext.Pipe;  
  18.         objSqlPipe.Send("Hi! I am simple CLR PROC");  
  19.   
  20.     }  
  21.     ///    
  22.     /// Proc to Show Rows of [EmployeeDB]..[Roles] table    
  23.     ///    
  24.   
  25.     [Microsoft.SqlServer.Server.SqlProcedure]  
  26.     public static void spGetRolesList()   
  27.     {  
  28.         //It returns rows from Roles table    
  29.         SqlConnection conn = new SqlConnection();  
  30.         conn.ConnectionString = "Context Connection=true";  
  31.   
  32.         SqlCommand cmd = new SqlCommand();  
  33.         cmd.Connection = conn;  
  34.         cmd.CommandText = @  
  35.         "Select * from [dbo].[Roles] Order By HireDate";  
  36.         conn.Open();  
  37.   
  38.         SqlDataReader sqldr = cmd.ExecuteReader();  
  39.         SqlContext.Pipe.Send(sqldr);  
  40.   
  41.         sqldr.Close();  
  42.         conn.Close();  
  43.   
  44.     }  
  45.     ///    
  46.     /// It shows rows from Employee table on basis of supplied age    
  47.     ///    
  48.     /// a specified age    
  49.   
  50.     [Microsoft.SqlServer.Server.SqlProcedure]  
  51.     public static void spGetEmployeeList(Int32 intAge)   
  52.     {  
  53.         //It returns rows from Employee table on basis of supplied age    
  54.         SqlConnection conn = new SqlConnection();  
  55.         conn.ConnectionString = "Context Connection=true";  
  56.   
  57.         SqlCommand cmd = new SqlCommand();  
  58.         cmd.Connection = conn;  
  59.         conn.Open();  
  60.         cmd.CommandText = "Select * from [dbo].[Employees] Where Age >=@intAge Order By Age";  
  61.         SqlParameter paramAge = new SqlParameter();  
  62.         paramAge.Value = intAge;  
  63.         paramAge.Direction = ParameterDirection.Input;  
  64.         paramAge.DbType = DbType.Int32;  
  65.         paramAge.ParameterName = "@intAge";  
  66.   
  67.         cmd.Parameters.Add(paramAge);  
  68.         SqlDataReader sqldr = cmd.ExecuteReader();  
  69.         SqlContext.Pipe.Send(sqldr);  
  70.   
  71.         sqldr.Close();  
  72.         conn.Close();  
  73.   
  74.     }  
  75. };  
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.
  1. Use [EmployeeDB]  
  2. Go  
  3.   
  4. DECLARE @Role int  
  5. SET @Role = 28  
  6. Exec [dbo].[spGetEmployeeList] @Role  
  7. Go  
  • The preceding code will display the EmployeeList with Employees having age 28 years or more.
  • In the same manner execute the other procedures.
  1. Use [EmployeeDB]  
  2. Go  
  3.   
  4. Exec [dbo].[myTestStoredProcedure]  
  5. Go  
  6.   
  7. Exec [dbo].[spGetRolesList]  
  8. Go  
  9.   
  10. DECLARE @Role int  
  11. SET @Role = 28  
  12. Exec [dbo].[spGetEmployeeList] @Role  
  13. Go  
Scripts of Databases

The following are the scripts of the Table(s) used in the given example:
  1. USE [master]  
  2. GO  
  3.   
  4. IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'EmployeeDB')  
  5. BEGIN  
  6. DROP DATABASE [EmployeeDB]  
  7. CREATE DATABASE [EmployeeDB]  
  8. END  
  9.   
  10. USE [EmployeeDB]  
  11. GO  
  12.   
  13. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[employees]'AND type in (N'U'))  
  14. BEGIN  
  15. DROP TABLE [dbo].[employees]  
  16.   
  17. CREATE TABLE [dbo].[employees](  
  18. [id] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
  19. [firstname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('First Name'),  
  20. [lastname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('Last Name'),  
  21. [age] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('19'),  
  22. PRIMARY KEY CLUSTERED  
  23. (  
  24. [id] ASC  
  25. )WITH (IGNORE_DUP_KEY = OFFON [PRIMARY]  
  26. ON [PRIMARY]  
  27. END  
  28.   
  29. GO  
  30.   
  31. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]'AND type in (N'U'))  
  32. BEGIN  
  33. DROP TABLE [dbo].[Roles]  
  34. CREATE TABLE [dbo].[Roles](  
  35. [Id] [int] IDENTITY(1,1) NOT NULL,  
  36. [Role] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,  
  37. [IsRetiree] [bitNOT NULL,  
  38. [HireDate] [datetime] NOT NULL,  
  39. CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED  
  40. (  
  41. [Id] ASC  
  42. )WITH (IGNORE_DUP_KEY = OFFON [PRIMARY]  
  43. ON [PRIMARY]  
  44. END  
  45.   
  46. 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:
  1. sp_configure 'clr enabled', 1  
  2. go  
  3. reconfigure  
  4. go  
  • You can also Debug the produced code within Visual Studio by starting debugging from the DEBUG MENU.