Passing Parameters to Procedures in MySQL
Handling MySQL Events with Triggers and Procedures Using SQL – Part 7
Forward: In this part of the series, we see how to pass parameters to procedures in MySQL.
By: Chrysanthus Date Published: 7 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Meaning of Parameter Passing
Imagine that you have a trigger that calls a procedure to carry out a certain task for the trigger. It should be possible for the trigger to send data to the procedure in the calling statement, so that the procedure can use the data to carry out the task the trigger wants. This process is called Passing Parameters to the Procedure.
Consider the following procedure:
Delimiter //
CREATE PROCEDURE sampleProcedure
(
IN par1 INTEGER,
IN par2 INTEGER
)
BEGIN
END;
//
With this procedure, we say the parentheses hold the declarations of the parameters. IN means that the parameters represent values that would be received from outside the procedure (when the procedure is called).
IN is a parameter mode. There are two other parameter modes, which are OUT and INOUT. We saw these in one of the previous parts of the series.
A compound statement such as that of a trigger can call a procedure, sending data to the procedure. The data passed are the variables of the compound statement. The following code segment illustrates this.
Delimiter //
BEGIN
DECLARE var1 INTEGER DEFAULT 25;
DECLARE var2 INTEGER DEFAULT 30;
CALL sampleProcedure (var1, var2);
END;
//
The CALL statement in the compound statement calls the procedure sending 25 and 30 with var1 and var2, respectively. The procedure will receive these numbers in the variables, par1 and par2. Note how the variable declarations have been done in the compound statement and note how the CALLING statement has been typed (the name of the procedure is included).
Note that the data types for the declarations in the compound statement must be the same as the corresponding data types for the procedure parameters (inside the procedure).
That is it for this part of the series. Let us stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT