Creating or Replacing an SQL Stored Procedure - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

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.