User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To invoke a procedure.

Syntax

     [variable = ] CALL procedure-name ( [ expression ,... ] )

     [variable = ] CALL procedure-name ( [ parameter-name = expression ,... ] )

Usage

Anywhere.

Permissions

Must be the owner of the procedure, have EXECUTE permission for the procedure, or have DBA authority.

Side effects

None.

See also

• CREATE PROCEDURE statement
• GRANT statement
• The chapter "Using Procedures, Triggers, and Batches"

Description

The CALL statement invokes a procedure that has been previously created with a CREATE PROCEDURE statement. When the procedure completes, any INOUT or OUT parameter values will be copied back.

The argument list can be specified by position or by using keyword format. By position, the arguments will match up with the corresponding parameter in the parameter list for the procedure. By keyword, the arguments are matched up with the named parameters.

All arguments are optional: procedure arguments can be assigned default values in the CREATE PROCEDURE statement, and missing parameters are assigned the default value or, if no default is set, the parameter is set to NULL.

Inside a procedure, a CALL statement can be used in a DECLARE statement when the procedure returns result sets (see "Returning results from procedures").

Procedures can return a value (as a status indicator, say) using the RETURN statement. You can save this return value in a variable using using the quality sign as an assignment operator:

     CREATE VARIABLE returnval INT ;
     returnval = CALL proc_integer ( arg1 = val1, ... )

Examples

Call the sp_customer_list procedure. This procedure has no parameters, and returns a result set.

     CALL sp_customer_list()

The following ISQL example creates a procedure to return the number of orders placed by the customer whose ID is supplied, creates a variable to hold the result, calls the procedure, and displays the result.

     % Set the statement delimiter to create the procedure

     SET OPTION COMMAND_DELIMITER = ';;'
     % Create the procedure
     CREATE PROCEDURE OrderCount (IN customer_ID INT, OUT Orders INT)
     BEGIN
     SELECT COUNT("DBA".sales_order.id)
     INTO Orders
     FROM "DBA".customer
     KEY LEFT OUTER JOIN "DBA".sales_order
     WHERE "DBA".customer.id = customer_ID ;
     END ;;
     % Reset the statement delimiter to semicolon.
     SET OPTION COMMAND_DELIMITER = ';'
     % Create a variable to hold the result
     CREATE VARIABLE Orders INT ;
     % Call the procedure, FOR customer 101
     %-----------------------------
     CALL OrderCount ( 101, Orders) ;
     %------------------------------
     % Display the result
     SELECT Orders FROM DUMMY ;