Usage Notes - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.