CLR Stored Procedures – Part 1: Highlights

New to .NET integration in SQL Server? Here is some useful information put together for you:

·         .NET integration is one of the key feature in SQL Server 2005

·         The CLR is hosted by SQL server thereby executing the stored procedures in SQL Server’s process space

·         CLR stored procedures uses ADO.NET objects (SqlCommand, SqlConnection, DataSet etc) to retrieve data from and write data into SQL Server

·         Microsoft.SqlServer.Server namespace provides the classes and attributes for supporting the managed CLR routines

·         An object called ‘SqlContext’ represents the connection instantiated by the CLR stored procedure and manages passing of parameters

·         ‘SqlContext’ is defined in the Microsoft.SqlServer.Server namespace

·         To create a CLR stored procedure

o       Create a Database Project in Visual Studio

o       Add a database reference

o       Add à Stored procedure to the project

·         The steps for creating the CLR stored procedure is explained in my blog here

·         The default entries created when you add a CLR stored procedure  using visual studio are shown below. (Stored procedure name is given as ‘GetSales’):


using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;



public partial class StoredProcedures



    public static void GetSales()


        // your code here




·         The [Microsoft.SqlServer.Server.SqlProcedure] attribute specifies that this method is a stored procedure

·         The method GetSales() is static, as it needs to be invoked without instantiation

·         The namespace System.Data.SqlTypes provides all the data types that correspond to each of the SQL Server data types (Example: SqlTypes.SqlDateTime, SqlTypes.SqlInt32 etc.)


CLR Stored Procedure Parameters


·         The parameters are passed by specifying them in the stored procedure method. Example is given below



    public static void GetSales(SqlDateTime StartDate, SqlInt32 Days)


        // your code here


·         Output parameters can be passed by using the ref keyword




·         SqlPipe is an object that allows us to send data or commands to be executed from a CLR routine back to the caller.


·         Send() method of the SqlPipe is used to send the data which has 3 overloads

            Send(string message)

            Send(SqlDataRecord record)

            Send(SqlDatareader reader)



·         Send() methods can be called any number of times during the course of a CLR stored procedure.



      command.CommandText = “Select * from MonthlySales”;

      SqlDataReader reader = command.EndExecuteReader();



·         ExecuteAndSend() method of the SqlPipe does take care of the execution and subsequent return of the result back to the caller. So the last 2 lines of the above example can be replaced with:




·         CLR stored procedure can return multiple result sets and multiple messages or errors


Error Handling


·         Use Try{…} Catch{..}

·         To throw custom exceptions, use RAISEERROR


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s