Stored Procedures

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 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

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.

Deleting a Procedure

One of the biggest 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.