SqlPipe Object

In previous versions of SQL Server, it is very common to write a stored procedure (or an extended stored procedure) that sends results or output parameters to the calling client.

In a Transact-SQL stored procedure, any SELECT statement that returns zero or more rows sends the results to the connected caller's "pipe."

For common language runtime (CLR) database objects running in SQL Server, you can send results to the connected pipe using the Send methods of the SqlPipe object. Access the Pipe property of the SqlContext object to obtain the SqlPipe object. The SqlPipe class is conceptually similar to the Response class found in ASP.NET. For more information, see the SqlPipe Class reference documentation in the .NET Framework software development kit.

Returning Tabular Results and Messages

The SqlPipe has a Send method, which has three overloads. They are:

• void Send(string message)

• void Send(SqlDataReader reader)

• void Send(SqlDataRecord record)

The Send method sends data straight to the client or caller. It is usually the client that consumes the output from the SqlPipe, but in the case of nested CLR stored procedures the output consumer can also be a stored procedure. For example, Procedure1 calls SqlCommand.ExecuteReader() with the command text "EXEC Procedure2". Procedure2 is also a managed stored procedure. If Procedure2 now calls SqlPipe.Send( SqlDataRecord ), the row is sent to Procedure1's reader, not the client.

The Send method sends a string message that appears on the client as an information message, equivalent to PRINT in Transact-SQL. It can also send a single-row result-set using SqlDataRecord, or a multi-row result-set using a SqlDataReader.

The SqlPipe object also has an ExecuteAndSend method. This method can be used to execute a command (passed as a SqlCommand object) and send results directly back to the caller. If there are errors in the command that was submitted, exceptions are sent to the pipe, but a copy is also sent to calling managed code. If the calling code does not catch the exception, it propagates up the stack to the Transact-SQL code and appears in the output twice. If the calling code does catch the exception, the pipe consumer still sees the error, but there is not a duplicate error.

It can only take a SqlCommand that is associated with the context connection; it cannot take a command that is associated with the non-context connection.

Returning Custom Result Sets

Managed stored procedures can send result sets that do not come from a SqlDataReader. The SendResultsStart method, along with SendResultsRow and SendResultsEnd, allows stored procedures to send custom result sets to the client.

SendResultsStart takes a SqlDataRecord as an input. It marks the beginning of a result set and uses the record metadata to construct the metadata that describes the result set. It does not send the value of the record with SendResultsStart. All the subsequent rows, sent using SendResultsRow, must match that metadata definition.

Note

After calling the SendResultsStart method only SendResultsRow and SendResultsEnd can be called. Calling any other method in the same instance of SqlPipe causes an InvalidOperationException. SendResultsEnd sets SqlPipe back to the initial state in which other methods can be called.

Example

The uspGetProductLine stored procedure returns the name, product number, color, and list price of all products within a specified product line. This stored procedure accepts exact matches for prodLine.

C#

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 uspGetProductLine(SqlString prodLine)  
{  
    // Connect through the context connection.  
    using (SqlConnection connection = new SqlConnection("context connection=true"))  
    {  
        connection.Open();  
  
        SqlCommand command = new SqlCommand(  
            "SELECT Name, ProductNumber, Color, ListPrice " +  
            "FROM Production.Product " +   
            "WHERE ProductLine = @prodLine;", connection);  
  
        command.Parameters.AddWithValue("@prodLine", prodLine);  
  
        try  
        {  
            // Execute the command and send the results to the caller.  
            SqlContext.Pipe.ExecuteAndSend(command);  
        }  
        catch (System.Data.SqlClient.SqlException ex)  
        {  
            // An error occurred executing the SQL command.  
        }  
     }  
}  
};  

Visual Basic

Imports System  
Imports System.Data  
Imports System.Data.SqlClient  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
  
Partial Public Class StoredProcedures  
<Microsoft.SqlServer.Server.SqlProcedure()> _  
Public Shared Sub uspGetProductLine(ByVal prodLine As SqlString)  
    Dim command As SqlCommand  
  
    ' Connect through the context connection.  
    Using connection As New SqlConnection("context connection=true")  
        connection.Open()  
  
        command = New SqlCommand( _  
        "SELECT Name, ProductNumber, Color, ListPrice " & _  
        "FROM Production.Product " & _  
        "WHERE ProductLine = @prodLine;", connection)  
        command.Parameters.AddWithValue("@prodLine", prodLine)  
  
        Try  
            ' Execute the command and send the results   
            ' directly to the caller.  
            SqlContext.Pipe.ExecuteAndSend(command)  
        Catch ex As System.Data.SqlClient.SqlException  
            ' An error occurred executing the SQL command.  
        End Try  
    End Using  
End Sub  
End Class  

The following Transact-SQL statement executes the uspGetProduct procedure, which returns a list of touring bike products.

EXEC uspGetProductLineVB 'T';  

See Also

SqlDataRecord Object
CLR Stored Procedures
SQL Server In-Process Specific Extensions to ADO.NET