Teradata Stored Procedures - Calling a Procedure within a Procedure

Calling Stored Procedures from another stored procedure:

We can call procedures with in a procedure. We can also pass parameters to it. Say we have two procedures as follows: REPLACE  PROCEDURE SUPPORT_PROCS.SQUARE_IT(IN VALUE1 integer,OUT squarevalue INTEGER) BEGIN set squarevalue = value1 * value1; END; CREATE  PROCEDURE SUPPORT_PROCS.CUBE_IT(IN VALUE1 integer,OUT cubevalue INTEGER) BEGIN set cubevalue = value1 * value1 * value1; END; Each of the procedures accepts a parameter and returns a value. SUPPORT_PROCS.SQUARE_IT provides the square. SUPPORT_PROCS.CUBE_IT provides the square. Then we create a procedure which calls these two procedures. The procedure uses the SQUARE_IT and CUBE_IT to accept a value and return square and cube. REPLACE PROCEDURE SUPPORT_PROCS.CALCULATE(IN value1 integer ,OUT squarevalue integer, OUT cubevalue integer) BEGIN CALL SUPPORT_PROCS.SQUARE_IT(:value1,:squarevalue); CALL SUPPORT_PROCS.CUBE_IT(:value1,:cubevalue); END; CREATE PRECEDURE and REPLACE PROCEDURE both mean the same. When using REPLACE, if the procedure already exists it replaces it and if it does not then it creates one. Things Learnt: When I ran the query to create the CALCULATE procedure the query fails saying owner does not have execute access on the procedures SQUARE_IT and CUBE_IT. So we need to provide the access as follows: GRANT EXECUTE PROCEDURE ON SUPPORT_PROCS.SQUARE_IT TO SUPPORT_PROCS WITH GRANT OPTION; GRANT EXECUTE PROCEDURE ON SUPPORT_PROCS.CUBE_IT TO SUPPORT_PROCS WITH GRANT OPTION; Note that the access needed is called 'EXECUTE PROCEDURE' access even when do don’t actually 'EXEC' a procedure, we CALL them. Also note that we need to provide access to the owner SUPPORT_PROCS even when all procedures are under same database. We call the procedure CALCULATE as Follows: CALL SUPPORT_PROCS.CALCULATE(11,val1,val2); squarevalue cubevalue 121 1331 Rules for CALLing a procedure within a procedure: Stored procedures are callable only from the platform they were created. Windows or UNIX. Procedures cannot call them selves. Procedures can be only called from the session modes in which they were created - ANSI or Teradata. If call statement is inside a MACRO then it must be only one statement in the macro. CALL statement may not be a part of  a multi statement request. Number of nested call statement is limited to 15