Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Data.SqlTypes


Public Class PubsProcs

    ' uses SqlPipe and least expensive way to return rows
    <SqlProcedure()> _
    Public Shared Sub getAuthorsByState(ByVal state As SqlString)

    End Sub

    ' Gets information about a single author
    ' and returns data in an OUTPUT parameter
    <SqlProcedure()> _
    Public Shared Sub getAuthorInfo(ByVal au_id As SqlString, ByRef au_info As SqlString)
        ' build a command

        ' build command text
        ' note that this will return a single row because
        ' au_id is the primary key

        ' make a paramter to hold the author id

        ' put in the value of the author id

        ' use ExecuteReader, expect
        ' a single row will come back

        ' au_info is marked 'out' so you must create
        ' a SqlString for it, as it is null when 
        ' getAuthorInfo is called

        ' use Format to build the information string

        ' nothing to return beacuse value is sent back via au_info
    End Sub
    ' Get author info
    ' use a SqlPipe to send info
    ' back as message
    <SqlProcedure()> _
    Public Shared Sub getAuthorMessage(ByVal au_id As SqlString)
        ' build a command

        ' build command text
        ' note that this will return a single row because
        ' au_id is the primary key

        ' make a paramter to hold the author id

        ' put in the value of the author id

        ' use use ExecuteReader, expect 
        ' a single row will come back

        ' get the SqlPipe from the SqlContext

        ' use the SqlPipe.Send to send back the
        ' author info as a message

    End Sub

    ' Previous function ignored an errors when
    ' query was executed
    ' Gets author info, but returns a customized error
    ' message if ssn passed in does not correspond
    ' to an author in pubs
    <SqlProcedure()> _
    Public Shared Function getAuthorWithErrors(ByVal au_id As SqlString, ByRef au_info As SqlString) As SqlInt32
        ' build a command

        ' build command text
        ' note that this will return a single row because
        ' au_id is the primary key

        ' make a paramter to hold the author id

        ' put in the value of the author id

        ' use use ExecuteReader, expect 
        ' a single row will come back


        ' make SqlString to hold result
        ' note that if you do not give this
        ' string a value it will be null

        ' check to see if lookup was successful
        If True = True Then
            'au_info = String.Format("Author {0} is not found", au_id);
            'cmd.CommandText = ...
            ' lookup was not successful, raise an error
            ' use the error that you defined in the database

            Return 0
        Else
            ' lookup was successful, set au_info to information string

        End If
        ' nothing to return, either success returned author info in au_info
        ' or error was raised
        Return 0
    End Function
End Class
