VB.NET • Return Rows With the GetFilteredContacts Stored Procedure

Listing 3. This CLR stored procedure takes a filter string as an input and returns rows from the Person.Contact table that are like the string pattern passed in.

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes

Partial Public Class StoredProcedures
        <SqlProcedure()> _
                Public Shared Sub GetFilteredContacts(ByVal strFilter As SqlString)
                ' Get the current connection, output pipe and SqlDataReader 
                ' for this execution context
                Dim objSqlCommand As SqlCommand = _
                        SqlContext.GetCommand()
                Dim objSqlPipe As SqlPipe = SqlContext.GetPipe()
                Dim objSqlDataReader As SqlDataReader
                Dim strCommand As String =  _
                        "SELECT LastName, FirstName, EMailAddress" & _
                        " FROM Person.Contact"
                ' Check to see if a filter string was passed in
                If Not (strFilter.IsNull) Then
                        ' Add filter to the query
                        strCommand &= " WHERE LastName LIKE '%" & _
                                strFilter.ToString.Trim & "%'"
                End If
                ' Execute the query
                objSqlCommand.CommandText = strCommand
                objSqlDataReader = objSqlCommand.ExecuteReader()
                ' Return the result
                objSqlPipe.Send(objSqlDataReader)
        End Sub
End Class