An example says it all.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void PerformDomainCount()
{
SqlConnection conn = new SqlConnection();
// 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.Connection = conn;
cmd.CommandText = "select * from customers";

SqlDataReader reader = cmd.ExecuteReader();

// Send result to caller
SqlContext.Pipe.Send( reader );

// Close the connection
conn.Close();
}