CLR Stored Procedure

It is possible in Sql Server to create a stored procedure in C# or Visual Basic. This is a tutorial/assignment in how to create a very simple procedure in C#:

1. Start a new project in Visual Studio.
2. Under C# choose Database as the kind of project.
3. Then Choose SQLServer Project.
4. If you have existing database connections, it will ask if you want to use one. If one to community assist exits use it. Other wise it will ask what kind of connection you want. Then it will open a new database connection dialog. Fill it in to look like the following:



5. Click OK. A dialog box will open telling you you need to enable clr/sql bugging:



6. Click Yes to continue.
7.Right click on the solution in the Solution Explorer and choose Add/Stored Procedure. Name it "ServiceSummary".



8. Enter this code:

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 ServiceSummary()
    {
        using (SqlConnection connect = new SqlConnection("context connection=true"))
        {
            connect.Open();
            SqlCommand cmd = connect.CreateCommand();
                cmd.CommandText=
                "Select ServiceName, sum(GrantAmount) as Total "
                + "From [Service] s "
                + "Inner Join ServiceGrant sg "
                + "On s.ServiceKey=sg.ServiceKey "
                + "Group by ServiceName";
                SqlContext.Pipe.ExecuteAndSend(cmd);
        }

      
    }
};

9. When you have the code in, build it.
10. Right click on the project in the Solution Explorer and choose "Deploy."
11. Open Sql Server Management Studio.
12. Look under stored procedures in CommunityAssist. You should see serviceSummary showing.
13. Before you can use the procedure, you must enable clr execution in Sql Server
to do that enter the following SQL:

sp_configure 'clr enabled", 1
Go
Reconfigure
Go

14.Execute the procedure by typing EXEC ServiceSummary in the query window