CLR Stored Procedures

Article
11/03/2006

Stored procedures are routines that cannot be used in scalar expressions. They can return tabular results and messages to the client, invoke data definition language (DDL) and data manipulation language (DML) statements, and return output parameters. Note that Microsoft Visual Basic does not support output parameters in the same way that Microsoft Visual C# does. You must specify to pass the parameter by reference and apply the <Out()> attribute to represent an output parameter, as in the following:

Public Shared Sub ExecuteToClient( <Out()> ByRef number As Integer)

Requirements for CLR Stored Procedures

In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly. The static method can either be declared as void, or return an integer value. If it returns an integer value, the integer returned is treated as the return code from the procedure. For example:

EXECUTE @return_status = procedure_name

The @return\_status variable contains the value returned by the method. If the method is declared void, the return code is 0.

If the method takes parameters, the number of parameters in the .NET Framework implementation should be the same as the number of parameters used in the Transact-SQL declaration of the stored procedure.

Parameters passed to a CLR stored procedure can be any of the native SQL Server types that have an equivalent in managed code. For the Transact-SQL syntax to create the procedure, these types should be specified with the most appropriate native SQL Server type equivalent. For more information about type conversions, see the topic "SQL Data Types and Their .NET Equivalents" in SQL Server 2005 Books Online.

Returning Results from CLR Stored Procedures

Information may be returned from CLR stored procedures in several ways. This includes output parameters, tabular results, and messages.

Output Parameters and CLR Stored Procedures

As with Transact-SQL stored procedures, information may be returned from CLR stored procedures using output parameters. The Transact-SQL DML syntax used for creating .NET Framework stored procedures is the same as that used for creating stored procedures written in Transact-SQL. The corresponding parameter in the implementation code in the .NET Framework class should use a pass-by-reference parameter as the argument.

Returning Tabular Results and Messages

Returning tabular results and messages to the client is done through the SqlPipe object, which is obtained by using the Pipe property of the SqlContext class. The SqlPipe object has a Send method. By calling the Send method, you can transmit data through the pipe to the calling application.

These are several overloads of the Send method, including one that sends a SqlDataReader and another that simply sends a text string.

Returning Messages

Use Send to send messages to the client application. The text of the message is limited to 8000 characters. If the message exceeds 8000 characters, it will be truncated.

Returning Tabular Results

To send the results of a query directly to the client, use the ExecuteAndSend method on the SqlPipe object. This is the most efficient way to return results to the client since the data is transferred to the network buffers without being copied into managed memory. For example:

[C#]

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class StoredProcedures 
{
   /// <summary>
   /// Execute a command and send the results to the client directly.
   /// </summary>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void ExecuteToClient()
   {
   using(SqlConnection connection = new SqlConnection("context connection=true")) 
   {
      connection.Open();
      SqlCommand command = new SqlCommand("select @@version", connection);
      SqlContext.Pipe.ExecuteAndSend(command);
      }
   }
}

[Visual Basic]

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures 
    ''' <summary>
    ''' Execute a command and send the results to the client directly.
    ''' </summary>
    <Microsoft.SqlServer.Server.SqlProcedure> _
    Public Shared Sub ExecuteToClient()
        Using connection As New SqlConnection("context connection=true")
            connection.Open()
            Dim command As New SqlCommand("SELECT @@VERSION", connection)
            SqlContext.Pipe.ExecuteAndSend(command)
        End Using
    End Sub
End Class

To send the results of a query that was executed previously through the in-process provider, or to pre-process the data using a custom implementation of SqlDataReader, use the overload of the Send method that takes a SqlDataReader. This method is slightly slower than the direct method described previously, but it offers greater flexibility to manipulate the data before it is sent to the client.

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class StoredProcedures 
{
   /// <summary>
   /// Execute a command and send the resultig reader to the client
   /// </summary>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void SendReaderToClient()
   {
      using(SqlConnection connection = new SqlConnection("context connection=true")) 
      {
         connection.Open();
         SqlCommand command = new SqlCommand("select @@version", connection);
         SqlDataReader r = command.ExecuteReader();
         SqlContext.Pipe.Send(r);
      }
   }
}

[Visual Basic]

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures 
    ''' <summary>
    ''' Execute a command and send the results to the client directly.
    ''' </summary>
    <Microsoft.SqlServer.Server.SqlProcedure> _
    Public Shared Sub SendReaderToClient()
        Using connection As New SqlConnection("context connection=true")
            connection.Open()
            Dim command As New SqlCommand("SELECT @@VERSION", connection)
            Dim reader As SqlDataReader
            reader = command.ExecuteReader()
            SqlContext.Pipe.Send(reader)
        End Using
    End Sub
End Class

To create a dynamic result set, populate it and send it to the client, you can create records from the current connection and send them using Send.

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 
using System.Data.SqlTypes;

public class StoredProcedures 
{
   /// <summary>
   /// Create a result set on the fly and send it to the client.
   /// </summary>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void SendTransientResultSet()
   {
      // Create a record object that represents an individual row, including it's metadata.
      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));
      
      // Populate the record.
      record.SetSqlString(0, "Hello World!");
      
      // Send the record to the client.
      SqlContext.Pipe.Send(record);
   }
}

[Visual Basic]

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures 
    ''' <summary>
    ''' Create a result set on the fly and send it to the client.
    ''' </summary>
    <Microsoft.SqlServer.Server.SqlProcedure> _
    Public Shared Sub SendTransientResultSet()
        ' Create a record object that represents an individual row, including it's metadata.
        Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )

        ' Populate the record.
        record.SetSqlString(0, "Hello World!")

        ' Send the record to the client.
        SqlContext.Pipe.Send(record)        
    End Sub
End Class

Here is an example of sending a tabular result and a message through SqlPipe.

using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class StoredProcedures 
{
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void HelloWorld()
   {
      SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");
      using(SqlConnection connection = new SqlConnection("context connection=true")) 
      {
         connection.Open();
         SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);
         SqlDataReader reader = command.ExecuteReader();
         SqlContext.Pipe.Send(reader);
      }
   }
}

[Visual Basic]

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures 
    ''' <summary>
    ''' Execute a command and send the results to the client directly.
    ''' </summary>
    <Microsoft.SqlServer.Server.SqlProcedure> _
    Public Shared Sub HelloWorld()
        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")
        Using connection As New SqlConnection("context connection=true")
            connection.Open()
            Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)
            Dim reader As SqlDataReader
            reader = command.ExecuteReader()
            SqlContext.Pipe.Send(reader)
        End Using
    End Sub
End Class

The first Send sends a message to the client, while the second sends a tabular result using SqlDataReader.

Note that these examples are for illustrative purposes only. CLR functions are appropriate for more computation-intensive applications rather than executing simple Transact-SQL statements. This Transact-SQL stored procedure is almost equivalent to the previous example:

CREATE PROCEDURE HelloWorld() AS
BEGIN
PRINT('Hello world!')
SELECT ProductNumber FROM ProductMaster
END

Note

Messages and result sets are retrieved differently in the client application. For instance, SQL Server Management Studio result sets appear in the Results view, and messages appear in the Messages pane.

You can register the assembly if the above code is saved in a file MyFirstUdp.cs or MyFirstUdp.vb and compiled with:

csc /t:library MyFirstUdp.cs /o: MyFirstUdp.dll

vbc /t:library MyFirstUdp.vb /o: MyFirstUdp.dll

The following DDL registers the resulting assembly, and invokes the entry point:

CREATE ASSEMBLY MyFirstUdp FROM 'MyFirstUdp.dll'
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.T.HelloWorld
EXEC MyFirstUdp

Share via

CLR Stored Procedures

Requirements for CLR Stored Procedures

Returning Results from CLR Stored Procedures

Output Parameters and CLR Stored Procedures

Returning Tabular Results and Messages

Returning Messages

Returning Tabular Results

Additional resources