Home

Commanding a Database

 

Stored Procedures

 

Introduction

In Lesson 5, we saw that a procedure was an assignment used to take care of a task. So far, when we created a procedure, we confined it to a module: the module in which it was created. Normally, such an assignment was intended to be used only by the object (such as a form) that owned that module, unless we decided to invoke that procedure somewhere else in the database. One of the main limitations of such a procedure is that it is not an object and it cannot be saved as such. ADO provides an alternative.

You can create a procedure and save it as an object. Although you may use the event of a button, a form, or another control to create the procedure, the object or control doesn't own the procedure. In fact, after creating the procedure, it becomes an object and you can access it from anywhere in the database. Also, the procedure is saved as an object (as done for a table, a query, or a form) and it is stored in the database. Because the procedure is stored as an object, it is referred to as a stored procedure.

Like a view, a stored procedure is not represented in the Database window. This means that, after creating, you should be aware of its existence or you should find a way to know what stored procedures are available in your database.

Creating a Stored Procedure

To create a stored procedure, start with the CREATE PROCEDURE expression. You can also use CREATE PROC. Both expressions produce the same result. Like everything in your database, you must name your stored procedure. The name of a stored procedure can be any string that follows the rules we reviewed for naming objects.

After the name of the procedure, type the AS keyword. The section, group of words, or group of lines after the AS keyword is called the body of the procedure. It states what you want the procedure to do or what you want it to produce. Based on this, the simplest syntax of defining a procedure is:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure

It is important to keep in mind that there are many other issues related to creating a procedure but for now, let's consider that syntax.

Probably the simplest procedure you can write would consist of selecting columns from a table. This is done with the SELECT keyword and applying the techniques we reviewed for data selection. A stored procedure can also be used to enter new records in a table or to update one or some values of one or some of the records. For example, imagine that a company is hiring some contractors and all of them would be paid at the same salary. You can create a stored procedure that would enter this salary into each existing record by simply updating them. The stored procedure can be created as follows:

Private Sub cmdPrepareNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                   "AS " & _
                   "Update Employees " & _
                   "SET HourlySalary = 12.50;"
    
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

This procedure would be used to visit each record and assign or change the hourly salary of the employee to 12.50.

Executing a Stored Procedure

After creating a procedure, to get its result, you would need to execute it. To execute a procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE ProcedureName

Alternatively, instead of EXECUTE, you can use the EXEC keyword:

EXEC ProcedureName

For example, to execute the above procedure, you would use code as follows:

Private Sub cmdNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "EXECUTE SetNewMinSalary;"
    
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

Arguments and Parameters

Like a regular function, a stored procedure can take one or more arguments. To create a procedure that takes an argument, type the formula CREATE PROCEDURE or CREATE PROC followed by the name of the procedure, then type the name of the argument. The parameter is created like a column of a table. That is, a parameter must have a name, a data type and an optional length. Here is the syntax you would use:

CREATE PROCEDURE ProcedureName
(ParameterName DataType)
AS
Body of the Procedure

When implementing the procedure, you can define what you want to do with the parameter(s), in the body of the procedure. Consider the following list of employees;

Imagine that you want to raise the salary of the employees who are still earning the minimum wage. Instead of simply setting everybody's minimum, you can create a stored procedure that would visit each record and, whenever it find a salary below your minimum, it would raise it. You can create such as procedure as follows:

CREATE PROCEDURE SetNewMinSalary 
(NewMinSalary Currency)
AS
	UPDATE Employees
        SET Salary = NewMinSalary
	WHERE HourlySalary < NewMinSalary

This can be done in code as follows:

Private Sub cmdPrepareNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                   "(NewMinSalary Currency) " & _
                   "AS " & _
                   "Update Employees " & _
                   "Set HourlySalary = NewMinSalary " & _
                   "WHERE HourlySalary < NewMinSalary;"

    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

When executing a procedure that takes a parameter, make sure you provide a value for the parameter. The syntax used is:

EXEC ProcedureName ParameterValue

If the parameter is Boolean or numeric, make sure you provide an appropriate value. If the parameter is a character or a string, type its value in single-quotes. If it is a number, provide a valid value. Here is an example:

Private Sub cmdSetNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "EXECUTE SetNewMinSalary 10.15;"
    
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

When this code executes, every employee whose salary is less than 10.15 would be raised to 10.15. The other would be kept as is. The above code would produce:

Another type of procedure can be made to take more than one parameter. In this case, create the parameters in the section before the AS keyword, separated by a comma. The syntax you would use is:

CREATE ProcedureName
(ParameterName1 DataType, ParameterName2 DataType, ParameterName_n DataType_n)
AS
Body of the Procedure

When calling a procedure that takes more than one parameter, you must still provide a value for each parameter but you have two alternatives. The simplest technique consists of providing a value for each parameter in the exact order they appear in the procedure.

Alternatively, you can provide the value for each parameter in the order of your choice. In this case, you must type the name of each parameter and assign it the corresponding value.

 

Practical LearningPractical Learning: Creating and Using a Stored Procedure With Arguments

  1. Start Microsoft Access and open the I Rent Stuff database you started in Lesson 23
  2. In the Forms section of the Database window, right-click Products and click Design View
  3. Access the properties of the form and click the Event tab
  4. Double-click On Load and click its ellipsis button
  5. Implement the event as follows:
     
    Private Sub Form_Load()
        Dim conEmployees As ADODB.Connection
        Dim strProcedure As String
        
        Set conEmployees = Application.CurrentProject.Connection
        
        strProcedure = "CREATE PROCEDURE SelectProductToRent " & _
                       "(ProdNbr Text) " & _
                       "AS " & _
                       "SELECT Make, Model, SerialNumber, "ProductCondition " & _
    		   "FROM Products " & _
                       "WHERE ProductNumber = ProdNbr;"
        conEmployees.Execute strProcedure
        
        strProcedure = "CREATE PROCEDURE SelectCustomer " & _
                       "(DriversLicenseNumber Text) " & _
                       "AS " & _
                       "SELECT FullName, Address, City, State, " & _
                       "PostalZIPCode, Country FROM Customers " & _
                       "WHERE DrvLicNbr = DriversLicenseNumber;"
        conEmployees.Execute strProcedure
        
        conEmployees.Close
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Close the Products form
  8. When asked whether you want to save the changes, click No
  9. In the Forms section of the Database window, right-click RentalOrders and click Design View
  10. On the form, click the SelectedProduct text box to select it
  11. In the Event tab of the Properties window, double-click On Lost Focus and click its ellipsis button
  12. Implement the event as follows:
     
    Private Sub ProductSelected_LostFocus()
    On Error GoTo ProductSelected_Error
    
        Dim conIRentStuff As ADODB.Connection
        Dim rstIRentStuff As ADODB.Recordset
        Dim strSelectProduct As String
        
        Set rstIRentStuff = New ADODB.Recordset
        Set conIRentStuff = CurrentProject.Connection
        
        If IsNull([ProductSelected]) Then Exit Sub
        
        strSelectProduct = "EXECUTE SelectProductToRent '" & ProductSelected & "'"
        rstIRentStuff.Open strSelectProduct, conIRentStuff, adOpenDynamic, _
    			adLockOptimistic, adCmdText
        
        [Make] = rstIRentStuff("Make")
        [Model] = rstIRentStuff("Model")
        [SerialNumber] = rstIRentStuff("SerialNumber")
        [ProductCondition] = rstIRentStuff("ProductCondition")
        
        rstIRentStuff.Close
        conIRentStuff.Close
        
        Set rstIRentStuff = Nothing
        Set conIRentStuff = Nothing
        
    TheEnd:
        Exit Sub
        
    ProductSelected_Error:
        If Err.Number = -2147352567 Then
            MsgBox "The product number you entered is not valid." & vbCrLf & _
                   "Please try again!"
            
            [Make] = ""
            [Model] = ""
            [CarYear] = ""
           
            Resume TheEnd
        Else
            MsgBox "An error occurred when retrieving the product information" & _
               vbCrLf & _
                   "Please call the program vendor and report the error as follows:" & _
               vbCrLf & _
                   "Error #: " & Err.Number & vbCrLf & _
                   "Reason:  " & Err.Description
            Resume TheEnd
        End If
    End Sub
  13. Return to Microsoft Access and click the Customer text box to select it
  14. In the Event tab of the Database window, double-click On Lost Focus and click its ellipsis button
  15. Implement the event as follows:
     
    Private Sub Customer_LostFocus()
    On Error GoTo Customer_Error
    
        Dim conIRentStuff As ADODB.Connection
        Dim rstIRentStuff As ADODB.Recordset
        Dim strSelectCustomer As String
        
        Set rstIRentStuff = New ADODB.Recordset
        Set conIRentStuff = CurrentProject.Connection
        
        If IsNull([Customer]) Then Exit Sub
        
        strSelectCustomer = "EXECUTE SelectCustomer  '" & Customer & "'"
        rstIRentStuff.Open strSelectCustomer, conIRentStuff, 
    			adOpenDynamic, adLockOptimistic, adCmdText
    
        [CustName] = rstIRentStuff("FullName")
        [CustAddress] = rstIRentStuff("Address")
        [CustCity] = rstIRentStuff("City")
        [CustState] = rstIRentStuff("State")
        [CustPostalCode] = rstIRentStuff("PostalZIPCode")
        [CustCountry] = rstIRentStuff("Country")
        
        rstIRentStuff.Close
        conIRentStuff.Close
        
        Set rstIRentStuff = Nothing
        Set conIRentStuff = Nothing
    
    TheEnd:
        Exit Sub
        
    Customer_Error:
        If Err.Number = -2147352567 Then
            MsgBox "The driver's license number you entered is not valid." & vbCrLf & _
                   "Please try again!"
                   
            [CustName] = ""
            [CustAddress] = ""
            [CustCity] = ""
            [CustState] = ""
            [CustPostalCode] = ""
            [CustCountry] = ""
            
            Resume TheEnd
        Else
            MsgBox "An error occurred when retrieving the car information" & _
               vbCrLf & _
                   "Please call the program vendor and report the error as follows:" & _
               vbCrLf & _
                   "Error #: " & Err.Number & vbCrLf & _
                   "Reason:  " & Err.Description
            Resume TheEnd
        End If
    End Sub
  16. Return to Microsoft Access
  17. Save and close the form

Deleting a Procedure

One of the strong characteristics of a stored procedure, as compared to functions in traditional languages, is that a stored procedure is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

To delete a procedure in SQL code, the formula to use is:

DROP PROCEDURE ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists.

 

Commands

 

Introduction

From what we have learned in previous sections and lessons, a SQL statement is an expression that is presented to the SQL interpreter that interprets it and returns a result. ADO proposes yet another way to formulate and execute such a statement. A command is an action that is created or formulated and submitted to the database. The action can be any of the types of SQL statements and queries we have used so far. That is, it can consist of creating a database or a table. It could also consist of selecting fields or restricting the list of values from a table.

Command Creation

In ADO, a command is an object of type Command. To create it, you can first declare a variable of type ADODB.Command  and initialize it using the New operator. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
End Sub

One of the characteristics of a command is the action it is supposed to perform. This is specified as a string and it is represented by the CommandText property of the Command object. It can be similar to any of the SQL statements we have used so far. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
End Sub

Alternatively, you can use the name of a table as the text of the command. The text of a command can also be the name of a stored procedure.

After setting the action, you should specify the type of command that will be performed. The types of commands are members of the CommandTypeEnum enumerator. If the command is text-based such as a SQL statement, you can specify the type as adCmdText. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
    cmdVideos.CommandType = adCmdText
    
End Sub

If the text of the command is specified as the name of a table, you can set the command type to adCmdTable. If the text of the command is represented by the name of a stored procedure, then you can specify the command type as adCmdStoredProc.

After using a Command object, you should close it. To do this, you can call its Close() method.

Command Execution

After creating a Command object, to use its action, you must execute it. To start, you should specify what connection would be used to pass it. To support this, the Command object is equipped with a property named ActiveConnection. You can assign a Connection object to this property. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim rstVideos As ADODB.Recordset
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
    cmdVideos.CommandType = adCmdText
    cmdVideos.ActiveConnection = CurrentProject.Connection
     
    rstVideos.Close
    Set rstVideos = Nothing
    Set cmdVideos = Nothing
End Sub

To execute a command, you can use the Command itself, a Connection, or a Recordset objects. To execute a command using the Command object, you can call its Execute() method and assign it to a Recordset object. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim rstVideos As ADODB.Recordset
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
    cmdVideos.CommandType = adCmdText
    cmdVideos.ActiveConnection = CurrentProject.Connection
        
    Set rstVideos = cmdVideos.Execute
    
    rstVideos.Close
    Set rstVideos = Nothing
    Set cmdVideos = Nothing
End Sub

Alternatively, you can create a command as a text-based SQL statement and pass it as the Source argument to the Open() method of the Recordset.


Previous Copyright 2005 Yevol