http://www.vb-helper.com/HowTo/storedpr.zip

	Purpose
Create and invoke a stored procedure

	Method
A stored procedure is a function stored inside a database. They are useful for several reasons. They let you change a function without recompiling the program and let you store database functionality with the data it manipulates. If the database is across the network, the stored procedure can improve performance by examining many records and only returning a small result.

The program creates the stored procedure by executing the CREATE PROCEDURE statement (the code in the example gets the statement from a control).

    ' Create the stored procedure.
    Private Sub cmdCreate_Click()
        ' Drop the procedure if it already exists.
        On Error Resume Next
        m_DBConnection.Execute "DROP PROCEDURE BookInfo"
        On Error GoTo 0
    
        ' Create the stored procedure.
        m_DBConnection.Execute _
            "CREATE PROCEDURE BookInfo(@title VARCHAR) AS" & vbCrLf & _
            "    SELECT * FROM Books WHERE Title = @title"
    
        cmdCreate.Enabled = False
        cboTitle.Enabled = True
    End Sub

The program executes the procedure when you select a book title from a ComboBox. It creates a Command object, adds a parameter to give the stored procedure the data it needs, and executes the command.

    ' Display information for this book.
    Private Sub cboTitle_Click()
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim result As String
    Dim txt As String
    Dim i As Integer
    
        ' Create a command object.
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = m_DBConnection
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "BookInfo"
        txt = cboTitle.Text
        cmd.Parameters.Append cmd.CreateParameter("title", adVarChar, _
            adParamInput, Len(txt), txt)
    
        ' Execute the command.
        Set rs = cmd.Execute
    
        ' Display the results.
        txt = ""
        For i = 0 To rs.Fields.Count - 1
            txt = txt & rs.Fields(i).Name & " = " & rs.Fields(i).Value & vbCrLf
        Next i
        lblResults.Caption = txt
    
        ' Close the recordset and free it and the command object.
        rs.Close
        Set rs = Nothing
        Set cmd = Nothing
    End Sub

	Disclaimer
This example program is provided "as is" with no warranty of any kind. It is
intended for demonstration purposes only. In particular, it does no error
handling. You can use the example in any form, but please mention
www.vb-helper.com.
