The following notes apply to the usage of FNC_CallSP:
- When making nested stored procedure calls, only one of those procedures can be an external procedure. For example, an external stored procedure cannot call a stored procedure that in turn calls an external stored procedure.
- To call a stored procedure from an external stored procedure that uses CLIv2 to execute SQL, the best practice is to submit a CALL statement using CLIv2 instead of using FNC_CallSP.
- Normally, a stored procedure can only execute statements corresponding to the access clause of the most restrictive procedure that called it. For example, consider the following:
- Stored procedure sp1 where the CREATE PROCEDURE statement specifies a data access clause of NO SQL (the default).
- Stored procedure sp2 where the CREATE PROCEDURE statement specifies a data access clause of MODIFIES SQL DATA.
If stored procedure sp1 calls stored procedure sp2, stored procedure sp2 cannot execute SQL statements because the caller is already restricted by the NO SQL data access clause.
However, if you use FNC_CallSP to call stored procedure sp2 from an external stored procedure, the data access clause of the external stored procedure is ignored and stored procedure sp2 can execute SQL statements that modify data.