VB.NET  •  Use SqlPipe in a .NET Stored Procedure

Listing 1. This simple stored procedure executes a parameterized query and returns the results through SqlPipe. You use the SqlContext class' static methods to obtain a SqlCommand instance in the current batch, then fill in parameters as you'd do with SqlClient. The SqlContext.GetPipe method obtains a pipe to the current TDS output stream.

' uses SqlDataReader and 
' returns results via SqlPipe
   <SqlProcedure()> _
      Public Shared Sub getAuthorsByState( _
      ByVal state As SqlString)

      ' get SqlCommand from context
      Dim cmd As SqlCommand
      cmd = SqlContext.GetCommand()

      ' set the text and parameters
      cmd.CommandText = _
         "select * from authors where " & _
         "state = @state"
      cmd.Parameters.Add( _
         "@state", SqlDbType.VarChar)
      cmd.Parameters(0).Value = state

      ' call ExecuteReader
      Dim rdr As SqlDataReader 
      rdr = cmd.ExecuteReader()

      ' send back the reader through SqlPipe
      ' SqlPipe is only available through context
      Dim pipe As SqlPipe = SqlContext.GetPipe()
      pipe.Send(rdr)
   End Sub