Filter
Top Products
CHAPTER 6 Using Procedures and Batches
247
CREATE VARIABLE returnval INTEGER ;
returnval = CALL myproc() ;
Returning results as procedure parameters
Procedures can return results to the calling environment in the parameters to
the procedure.
Within a procedure, parameters and variables can be assigned values in one of
the following ways:
• The parameter can be assigned a value using the SET statement.
• The parameter can be assigned a value using a SELECT statement with an
INTO clause.
Using the SET
statement
The following somewhat artificial procedure returns a value in an OUT
parameter that is assigned using a SET statement:
CREATE PROCEDURE greater (IN a INT,
IN b INT,
OUT c INT)
BEGIN
IF a > b THEN
SET c = a;
ELSE
SET c = b;
END IF ;
END
Note The preceding example is artificial: generally a function is easier to use
than a procedure when only one result is required.
Using single-row
SELECT statements
Single-row queries retrieve at most one row from the database. This type of
query is achieved by a
SELECT statement with an INTO clause. The INTO
clause follows the select list and precedes the FROM clause. It contains a list of
variables to receive the value for each select list item. There must be the same
number of variables as there are select list items.