User's Guide

Part III. Using SQL Anywhere

Chapter 20. Using Procedures, Triggers, and Batches

The body of a procedure or trigger consists of a compound statement as discussed in "Using compound statements". A compound statement consists of a BEGIN and an END, enclosing a set of SQL statements. The statements must be delimited by semicolons.

The SQL statements that can occur in procedures and triggers are described in "SQL statements allowed in procedures and triggers".

Procedures and triggers can contain control statements, which are described in "Control statements".

SQL statements allowed in procedures and triggers

Many SQL statements are allowed within procedures and triggers, including the following:

• SELECT, UPDATE, DELETE, INSERT and SET variable.
• The CALL statement to execute other procedures.
• Control statements (see "Control statements").
• Cursor statements (see "Using cursors in procedures and triggers").
• Exception handling statements (see "Using exception handlers in procedures and triggers").
• The EXECUTE IMMEDIATE statement.

Some SQL statements are not allowed within procedures and triggers. These include the following:

• CONNECT statement
• DISCONNECT statement.

COMMIT, ROLLBACK and SAVEPOINT statements are allowed within procedures and triggers with certain restrictions (see "Transactions and savepoints in procedures and triggers").

For details, see the Usage for each SQL statement in the chapter "Watcom-SQL Language Reference".

Declaring procedure parameters

Procedure parameters, or arguments, are specified as a list in the CREATE PROCEDURE statement. Parameter names must conform to the rules for other database identifiers such as column names. They must be one of the types supported by SQL Anywhere (see "SQL Anywhere Data Types"), and must be prefixed with one of the keywords IN, OUT or INOUT. These keywords have the following meanings:

• IN The argument is an expression that provides a value to the procedure.
• OUT The argument is a variable that could be given a value by the procedure.
• INOUT The argument is a variable that provides a value to the procedure, and could be given a new value by the procedure.

Default values can be assigned to procedure parameters in the CREATE PROCEDURE statement. The default value must be a constant, which may be NULL. For example, the following procedure uses the NULL default for an IN parameter to avoid executing a query that would have no meaning:

     CREATE PROCEDURE
     CustomerProducts( IN customer_id
                             INTEGER DEFAULT NULL )
     RESULT ( product_id INTEGER,
                 quantity_ordered INTEGER )
     BEGIN
         IF customer_id IS NULL THEN
             RETURN;
         ELSE
             SELECT     product.id,
                         sum( sales_order_items.quantity )
             FROM    product,
                     sales_order_items,
                     sales_order
             WHERE sales_order.cust_id = customer_id
             AND sales_order.id = sales_order_items.id
             AND sales_order_items.prod_id=product.id
             GROUP BY product.id;
         END IF;
     END

The following statement causes the DEFAULT NULL to be assigned, and the procedure RETURNs instead of executing the query.

     CALL customer_products();

Passing parameters to procedures

You can take advantage of default values of stored procedure parameters with either of two forms of the CALL statement.

If the optional parameters are at the end of the argument list in the CREATE PROCEDURE statement, they may be omitted from the CALL statement. As an example, consider a procedure with three INOUT parameters:

     CREATE PROCEDURE SampleProc( INOUT var1 INT
                                             DEFAULT 1,
                                 INOUT var2 int DEFAULT 2,
                                 INOUT var3 int DEFAULT 3 )
     ...

We assume that the calling environment has set up three variables to hold the values passed to the procedure:

     CREATE VARIABLE V1 INT;
     CREATE VARIABLE V2 INT;
     CREATE VARIABLE V3 INT;

The procedure SampleProc may be called supplying only the first parameter as follows:

     CALL SampleProc( V1 )

in which case the default values are used for var2 and var3.

A more flexible method of calling procedures with optional arguments is to pass the parameters by name. The SampleProc procedure may be called as follows:

     CALL SampleProc( var1 = V1, var3 = V3 )

or as follows:

     CALL SampleProc( var3 = V3, var1 = V1 )

and so on.

Passing parameters to functions

User-defined functions are not invoked with the CALL statement, but are used in the same manner that built-in functions are. For example, the following statement uses the fullname function defined in "Creating user-defined functions" to retrieve the names of employees:

To list the names of all employees:

• Type the following:

       SELECT fullname(emp_fname, emp_lname) AS Name
       FROM employee
  

Name

Fran Whitney

Matthew Cobb

Philip Chin

Julie Jordan

Robert Breault

...

Notes

• Default parameters can be used in calling functions. However, parameters cannot be passed to functions by name.
• Parameters are passed by value, not by reference. Even if the function changes the value of the parameter, this change is not returned to the calling environment.
• Output parameters cannot be used in user-defined functions.
• User-defined functions cannot return result sets.