C#
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures {
  [Microsoft.SqlServer.Server.
  SqlProcedure()]
  public static void SampleSP() {
   // as usual, connection strings shouldn't
   // be hardcoded for production code
   using(SqlConnection conn = 
         new SqlConnection("server=MyServer;
           database=AdventureWorks; "
         + "user id=MyUser; password=MyPassword")) {
   conn.Open();
   SqlCommand cmd = new SqlCommand(
      "SELECT Name, GroupName FROM
      HumanResources.Department", conn);
   SqlDataReader r = cmd.ExecuteReader();
   while(r.Read()) {
        // consume the data from the reader
        // and perform some processing
   }
  }
 }
}

Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
  <Microsoft.SqlServer.Server.SqlProcedure()> _
  Public Shared Sub SampleSP()
   Dim cmd As SqlCommand
   Dim r As SqlDataReader
   ' as usual, connection strings shouldn't 
   ' be hardcoded for production code
   Using conn As New SqlConnection( _
    "server=MyServer; 
    database=AdventureWorks; 
    user id=MyUser; password=MyPassword")
    conn.Open()
    cmd = New SqlCommand( _
        "SELECT Name, GroupName FROM
        HumanResources.Department", conn)
    r = cmd.ExecuteReader()
    Do While r.Read()
     ' consume the data from the reader and 
     ' perform some processing
    Loop
   End Using
  End Sub
End Class