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

Basic Teradata® Query Reference

prodname
Basic Teradata Query
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-2414-220K

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.