ASA SQL Reference
  SQL Statements

CALL statement

Description 

Use this statement to invoke a procedure.

Syntax 1 

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

Syntax 2 

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

Usage 

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.

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, and an argument is not provided, an error is given.

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 an integer value (as a status indicator, say) using the RETURN statement. You can save this return value in a variable using the equality sign as an assignment operator:

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

For information on returning non-integer values, see CREATE FUNCTION statement.

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

EXECUTE statement [T-SQL]

Using Procedures, Triggers, and Batches

Standards and compatibility 

• SQL/92    Persistent Stored Module feature.

• SQL/99    Persistent Stored Module feature.

• Sybase    Not supported by Adaptive Server Enterprise. For an alternative that is supported, see EXECUTE statement [T-SQL].

Example 

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

CALL sp_customer_list()

The following Interactive SQL 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.

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
go
 -- Create a variable to hold the result
CREATE VARIABLE Orders INT
go
-- Call the procedure, FOR customer 101
CALL OrderCount ( 101, Orders)
go
--  Display the result
SELECT Orders FROM DUMMY
go