Stored procedures
All of the samples I used above were based on stored procedures. Stored procedures can be used to obtain and change data both on the local server and in remote data sources.

Stored procedures can also send results to the client, just like T-SQL stored procedures do. For example, in T-SQL you can have a stored procedure that does this:

CREATE PROCEDURE GetVendorsMinRating
    (@rating INT)
AS
SELECT VendorID, AccountNumber, 
    Name FROM Purchasing.Vendor
WHERE CreditRating <= @rating

A client running this stored procedure will see result sets coming back to the client (i.e., you would use ExecuteReader and get a SqlDataReader back if you were using ADO.NET in the client as well).

Managed stored procedures can return result sets, too. For stored procedures that are dominated by set-oriented statements such as the example above, using T-SQL is always a better choice. However, if you have a stored procedure that does a lot of computation-intensive work or uses a managed library and then returns some results, it may make sense to use SQLCLR.

Here is the same procedure rewritten in SQLCLR:

C#
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.
    SqlProcedure()]
  public static void SampleSP
       (int rating) {
   using (SqlConnection conn = new
     SqlConnection("context 
     connection=true")) {
    conn.Open();
    SqlCommand cmd = new SqlCommand(
      "SELECT VendorID, AccountNumber, 
       Name FROM Purchasing.Vendor " +
       WHERE CreditRating <= @rating",
       conn);
   cmd.Parameters.AddWithValue
      ("@rating", rating);
   // execute the command and send the
   //  results directly to the client
   SqlContext.Pipe.ExecuteAndSend(cmd);
  }
 }
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.
     SqlProcedure()> _
  Public Shared Sub SampleSP
   (ByVal rating As Integer)
   Dim cmd As SqlCommand
   ' connect to the context connection
   Using conn As New SqlConnection
     ("context connection=true")
    conn.Open()
    cmd = New SqlCommand( _
      "SELECT VendorID, AccountNumber,
       Name FROM Purchasing.Vendor " & _
      "WHERE CreditRating <= 
       @rating", conn)
    cmd.Parameters.AddWithValue
      ("@rating", rating)
    ' execute the command and send the 
    ' results directly to the client
    SqlContext.Pipe.ExecuteAndSend(cmd)
   End Using
  End Sub
End Class