How to create CLR store procedure

3.      You can add another connection by clicking “Add New Reference…” following screen will appear on click of button. The same screen will appear if you do not have any connection created previously.

4.      Type machine name or IP address of your SQL Server. Select type of authentication you required (here I am using windows authentication), then select database name where you would like to deploy CLR store procedure.

5.      Click on “Test Connection” if you got message “Test Connection Succeeded” then click on “OK”, else make sure you have selected proper SQL Server, database and you hold account on SQL Server for defined database.

6.      On click of “OK” you will see message window shown below.

7.      Say “Yes” and proceed, I will cover how to debug and set permissions, related to CLR store procedure in my next article named “Deploy and Debug CLR store procedure”.

8.      Now in solution click on project and say “Add New Item”, following window appears where as select item of type “Store Procedure” and click on “ADD”.  (I will cover what “Test Script” folder hold in my next article named “Deploy and Debug CLR store procedure”)

9.      Now in solution click on project and say “Add New Item”, following window appears where as select item of type “Store Procedure” and click on “ADD”.  (I will cover what “Test Script” folder hold in my next article named “Deploy and Debug CLR store procedure”)

10.  Cheer up, you are about to write your first store procedure. Here I will show you simple demo of how to select records using CLR store procedure, and it goes here.

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetRole()

    {

        // Put your code here

SqlConnection conn = new SqlConnection("Context    Connection=true");

SqlCommand cmd = new SqlCommand(@"Select * from dbo.Roles", conn);

        conn.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        SqlContext.Pipe.Send(rdr);

        rdr.Close();

        conn.Close();

  }

11.  Now select project from solution explorer and say deploy. Once deployed on SQL Server, go to SQL Server and fire following query and see the output.

     USE [IRSDev]

exec GetRole

Output :

12.  You may not able to deploy CLR store procedure in single go, since lot of permission issues. If so please refer my next article named “Deploy and Debug CLR store procedure”.