.NET Managed Stored Procedures in SQL Server 2005


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

CREATE ASSEMBLY SqlServerProcs FROM N'C:\dev\SqlServerProcs\SqlServerProcs\bin\Debug\SqlServerProcs.dll'
WITH PERMISSION_SET = SAFE


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

CREATE PROCEDURE GetAuthors AS EXTERNAL NAME SqlServerProcs.StoredProcs.GetAuthors

The external name parameter in the above command has the following syntax;

assemblyname.namespace/classname.methodname

  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.

EXEC dbo.GetAuthors

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

5 thoughts on “.NET Managed Stored Procedures in SQL Server 2005

  1. When i run Create Assembly:
    CREATE ASSEMBLY SqlServerProcs FROM N‘C:\dev\SqlServerProcs\SqlServerProcs\bin\Debug\SqlServerProcs.dll’
    WITH PERMISSION_SET = SAFE

    I got an error:
    CREATE ASSEMBLY failed because it could not open the physical file ‘C:\Documents and Settings\bin\SqlServerProcs.dll’: 2(The system cannot find the file specified.).

    Please help!
    edcon

  2. Great Read! Thanks for the bit of knowledge. I had a small issue implementing this but I put a call in my tech at Server Intellect and they resolve the SQL issue right away. Thanks again.

  3. When i tried to create a procedure which accesses the method, i get the following error. Can any one help me
    CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s