---
title: "SqlPipe Object | Microsoft Docs"
ms.custom: ""
ms.date: "03/14/2017"
ms.prod: sql
ms.prod_service: "database-engine"
ms.reviewer: ""
ms.technology: clr
ms.topic: "reference"
helpviewer_keywords: 
  - "custom result sets [CLR integration]"
  - "SqlPipe object"
  - "tabular results"
ms.assetid: 3e090faf-085f-4c01-a565-79e3f1c36e3b
author: "rothja"
ms.author: "jroth"
---
# SqlPipe Object
[!INCLUDE[appliesto-ss-xxxx-xxxx-xxx-md](../../includes/appliesto-ss-xxxx-xxxx-xxx-md.md)]
  In previous versions of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], 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 [!INCLUDE[tsql](../../includes/tsql-md.md)] 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], 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 [!INCLUDE[tsql](../../includes/tsql-md.md)]. 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 [!INCLUDE[tsql](../../includes/tsql-md.md)] 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 [!INCLUDE[tsql](../../includes/tsql-md.md)] statement executes the **uspGetProduct** procedure, which returns a list of touring bike products.  
  
```  
EXEC uspGetProductLineVB 'T';  
```  
  
## See Also  
 [SqlDataRecord Object](../../relational-databases/clr-integration-data-access-in-process-ado-net/sqldatarecord-object.md)   
 [CLR Stored Procedures](https://msdn.microsoft.com/library/bbdd51b2-a9b4-4916-ba6f-7957ac6c3f33)   
 [SQL Server In-Process Specific Extensions to ADO.NET](../../relational-databases/clr-integration-data-access-in-process-ado-net/sql-server-in-process-specific-extensions-to-ado-net.md)