To develop a managed stored procedure is relatively simple, however you first need to configure SQL Server 2005 to enable CLR Integration. You can do this using the SQL Server Surface Area Configuration tool, or entering the following command into SQLServer Management Studio;

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE WITH OVERRIDE;

GO 

Next you need to write a C# class library to implement the stored procedure behaviour. Create a standard C# class library as shown below, you’ll need to
import the Microsoft.SqlServer.Server namespace as well as the usual Sql namespaces.

CLR stored procedure are implemented in your code as static class instance methods, which return either an Int32 or void.
The method must be decorated with the [Microsoft.SqlServer.Server.SqlProcedure] attribute.

using System; 

using System.Collections.Generic; 

using System.Text; 

using System.Data; 

using System.Data.Sql; 

using System.Data.SqlClient; 

using System.Data.SqlTypes; 

using Microsoft.SqlServer.Server; 

public class StoredProcs 

{ 

   [Microsoft.SqlServer.Server.SqlProcedure] 

   public static void GetAuthors() 

   { 

      SqlPipe sp = SqlContext.Pipe; 

      using(SqlConnection conn = new SqlConnection("context connection=true")) 

      { 

         SqlCommand cmd = new SqlCommand("Select * from authors", conn); 

         conn.Open(); 

         SqlDataReader rdr = cmd.ExecuteReader(); 

         sp.Send(rdr); 

      } 

   } 

}

Notice that in this example I haven’t used namespaces (see below for namespaces). Build your assembly.

Next, you need to register the assembly in SQL Server 2005 with the following command

Then, you need to create the stored procedure linkage between a Stored Procedure name and your managed stored procedure implementation, as shown below.

  Using Namespaces.

If you defined your stored procedure implementation without using namespaces,
the “namespace/classname” part is simply the class name. If you did use namespaces,
the “namespace/classname” part is written as either "namespace.classname"
or [namespace.classname]. Assuming the above example had used a namespace name of "MyProcs" the CREATE PROCEDURE command would be written as follows;

CREATE PROCEDURE GetAuthors AS EXTERNAL NAME SqlServerProcs.[MyProcs.StoredProcs].GetAuthors

This stored procedure can now be executed just like any other normal stored procedure.