16.20 - Creating Stored Procedures - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
You can create a stored procedure using either of the following:
  • SQL CREATE PROCEDURE or REPLACE PROCEDURE statement in Teradata Studio Express.
  • COMPILE command with the BTEQ utility.

The procedures are stored in the user database space as objects and are executed on the server.

The stored procedure definitions in the next examples are designed only to demonstrate the usage of the feature. They are not recommended for use.

Example: Defining a Stored Procedure for New Employees

Assume you want to define a stored procedure NewProc to add new employees to the Employee table and retrieve the name of the department to which the employee belongs.

You can also report an error, in case the row that you are trying to insert already exists, and handle that error condition.

The CREATE PROCEDURE statement looks like this:

CREATE PROCEDURE NewProc (IN name CHAR(12),
                          IN number INTEGER,
                          IN dept INTEGER,
                          OUT dname CHAR(10))
BEGIN
   INSERT INTO Employee (EmpName, EmpNo, DeptNo )
      VALUES (name, number, dept);
   SELECT DeptName
      INTO dname FROM Department
         WHERE DeptNo = dept;
END;

This stored procedure defines parameters that must be filled in each time it is called.

Related Topics

For information on CREATE PROCEDURE and REPLACE PROCEDURE, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.