Calling a Stored Procedure¶
You can call a stored procedure using the SQL CALL command.
In order for a user to call a stored procedure, the user’s role must have the USAGE privilege for the stored procedure.
Once you have the privileges to call the stored procedure, you can use the CALL statement to call the stored procedure. For example:
CALL myProc('table_a', 'table_b', 5);
Note
To both create and call an anonymous procedure, use CALL (with Anonymous Procedure). Creating and calling an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.
Examples¶
To execute a stored procedure, use a CALL statement. For example:
call stproc1(5.14::FLOAT);
Each argument to a stored procedure can be a general expression:
CALL stproc1(2 * 5.14::FLOAT);
An argument can be a subquery:
CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);
You can call only one stored procedure per CALL statement. For example, the following statement fails:
call proc1(1), proc2(2); -- Not allowed
Also, you cannot use a stored procedure CALL as part of an expression. For example, all the following statements fail:
call proc1(1) + proc1(2); -- Not allowed
call proc1(1) + 1; -- Not allowed
call proc1(proc2(x)); -- Not allowed
select * from (call proc1(1)); -- Not allowed
However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively.
Caution
Nested calls can exceed the maximum allowed stack depth, so be careful when nesting calls, especially when using recursion.