16.10 - Creating Stored Procedures - Teradata Database

A stored procedure can be created from:

• BTEQ utility using the COMPILE command
• CLIv2 applications, ODBC, JDBC, and Teradata SQL Assistant (formerly called Queryman) using the SQL CREATE PROCEDURE or REPLACE PROCEDURE statement.

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

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 the syntax of data definition statements related to stored procedures, including CREATE PROCEDURE and REPLACE PROCEDURE, see SQL Data Definition Language - Syntax and Examples, B035-1144.