Another example.

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void PerformDomainCount()
  {
    SqlConnection conn = new SqlConnection();
    SqlDataReader reader = null;
    try
    {

    // use "Context Connection=true" to specify that you're
    // tagging along on the current connection
    // if you wish you could connect to an external
    // database
    conn.ConnectionString = "Context Connection=true";

    // You have to open the connection, which feels a little strange
    conn.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "select count(*), dbo.ExtractDomain(email) from customers group by dbo.ExtractDomain(email)";
    cmd.Connection = conn;

    reader = cmd.ExecuteReader();
    SqlMetaData[] meta = DefineMetaData();
    SqlDataRecord record = new SqlDataRecord( meta );
    if ( reader.HasRows )
    {
      reader.Read();
      Copy( reader, record );
      SqlContext.Pipe.SendResultsStart( record );
      SqlContext.Pipe.SendResultsRow( record );
      while ( reader.Read() )
      {
        Copy( reader, record );
        SqlContext.Pipe.SendResultsRow( record );
      }
    }

    }
    catch ( Exception exception )
    {
      SqlContext.Pipe.Send( "ERROR: " + exception.Message );
    }
    finally
    { 
      // Close all
      reader.Close();
      conn.Close();
      if ( SqlContext.Pipe.IsSendingResults )
      {
        SqlContext.Pipe.SendResultsEnd();
      }
    }
  }

  private static SqlMetaData[] DefineMetaData()
  {
    SqlMetaData[] meta = new SqlMetaData[2];
    meta[0] = new SqlMetaData( "Count", SqlDbType.Int );
    meta[1] = new SqlMetaData( "Domain", SqlDbType.NVarChar, 50 );
    return meta;
  }

  private static void Copy(SqlDataReader reader, SqlDataRecord record)
  {
    record.SetSqlInt32( 0, reader.GetSqlInt32( 0 ) );
    record.SetSqlString( 1, reader.GetSqlString( 1 ) );
  }
}