How to Create or Replace an SQL Stored Procedure | Basic Teradata Query - Creating or Replacing an SQL Stored Procedure - Basic Teradata Query

To create an SQL stored procedure, invoke a text editor to create a file named example.spl, and enter the SQL stored procedure source text:

CREATE PROCEDURE samplesp1 ()
BEGIN
   /* SPL Statements*/
DECLARE V1 INTEGER;
SET V1 = 100;
END;

The preceding example creates a procedure without parameters.

To replace this with another SQL stored procedure with parameters, create a file named example1.spl, and enter the SQL stored procedure source text:

REPLACE PROCEDURE samplesp1 (IN pAccountNo INTEGER, OUT pAmount DECIMAL (10,2))
BEGIN
   /* SPL Statements*/
   SELECT : Amount
   INTO : pAmount
   FROM : Account1 WHERE
   aCCTnO = :pAccountNo;
END;

Submit the COMPILE command with this file name.