CALL Statement

Quick Links:

Syntax

Syntax 1

[ <variable> = ] CALL <procedure-name> ( [ <expression> ] [ , … ] )

Syntax 2

[ <variable> = ] CALL <procedure-name> ( [ <parameter-name> = <expression> ] [ , … ] )

Examples

(back to top)

Example 1
calls the sp_customer_list procedure. This procedure has no parameters, and returns a result set:
```
CALL sp_customer_list()
```

Example 2

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 CustomerID INT, OUT Orders INT)
BEGIN
SELECT COUNT("DBA".SalesOrders.ID)
INTO Orders
FROM "DBA".Customers
KEY LEFT OUTER JOIN "DBA".SalesOrders
WHERE "DBA".Customers.ID = CustomerID ;
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

Usage

(back to top)

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

You can specify the argument list by position or by using keyword format. By position, arguments match up with the corresponding parameter in the parameter list for the procedure. By keyword, arguments match 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, NULL.

Inside a procedure, CALL can be used in a DECLARE statement when the procedure returns result sets.

Note

You cannot reference a Table UDF in a CALL SQL statement.

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, ... )

Note Use of this statement to invoke a function is deprecated. To call functions, use an assignment statement to invoke the function and assign its result to a variable. For example:

DECLARE varname INT;
SET varname=test( );

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Database products—Not supported by SAP ASE. For an alternative that is supported, see EXECUTE Statement [ESQL].

Permissions

(back to top)

Requires one of:

EXECUTE ANY PROCEDURE system privilege.
EXECUTE permission for the procedure.
You own the procedure.