Teradata Stored Procedure

A stored procedure contains a set of SQL statements and procedural statements. They consist of a set of control and condition handling comments that make SQL a computationally complete programming language. The definition of the stored procedure is stored in the database, and the parameters are stored in data dictionary tables.

These features provide a server-based procedural interface to Teradata Database for application programmers.

Stored procedures can be a great benefit for some tactical query applications. This section provides:

Some examples of using stored procedures to process complex updates.
A comparison of the relative efficiency of stored procedures and macros for different tactical query applications.

Advantages

Teradata Stored Procedure provides the following essential advantages, such as:

Stored procedures reduce the network load between the client and the server.
It provides better security since the data is accessed through stored procedures instead of accessing them directly.
It gives better maintenance since the business logic is tested and stored in the server.

Elements of Stored Procedures

The set of statements constituting the main tasks of the stored procedure is called the stored procedure body, consisting of a single statement or a compound statement or block.

A single statement stored procedure body can contain one control statement, such as LOOP or WHILE, or one SQL DDL, DML, or DCL statement, including dynamic SQL. The following statements are not allowed, such as:

Any declaration statement, such as local variable, cursor, condition, or condition handler.
A cursor statement (OPEN, FETCH, or CLOSE).

A compound statement stored procedure body consists of a BEGIN-END statement enclosing a set of declarations and statements, including:

Local variable declarations.
Cursor declarations.
Condition declarations.
Condition handler declaration statements.
Control statements.
SQL DML, DDL, and DCL statements supported by stored procedures, including dynamic SQL.
Multi-statement requests (including dynamic multi-statement requests) delimited by the keywords BEGIN REQUEST and END REQUEST.

Creating a Stored Procedure

Teradata stored procedure is created from the following commands:

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.
Stored Procedures are created using the CREATE PROCEDURE statement.

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

Syntax

Following is the syntax of the CREATE PROCEDURE statement.

CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) 
BEGIN 
   <SQL or SPL statements>; 
END;

Example

Consider the following Salary Table of the employees.

EmployeeId	Gross	Deduction	NetPay
202001	40,000	2,000	38,000
202002	80,000	3,000	77,000
202003	85,000	5,000	80,000
202004	90,000	2,000	88,000

The following example creates a stored procedure named InsertSalary to accept the values and insert them into the Salary Table.

CREATE PROCEDURE InsertSalary 
( 
   IN in_EmployeeId INTEGER, 
   IN in_Gross INTEGER, 
   IN in_Deduction INTEGER, 
   IN in_NetPay INTEGER 
) 
BEGIN 
   INSERT INTO Salary 
( 
      EmployeeId, 
      Gross, 
      Deduction, 
      NetPay 
 ) 
   VALUES 
( 
      :in_EmployeeId, 
      :in_Gross, 
      :in_Deduction, 
      :in_NetPay 
  );
 
END;

Executing Stored Procedures

If we have sufficient privileges, we can execute a stored procedure from any supporting client utility or interface using the SQL CALL statement.

We can also execute a stored procedure from an external stored procedure written in C, C++, or Java. We have to specify arguments for all the parameters contained in the stored procedure.

Syntax

Following is the generic syntax of the CALL statement.

Example

The below example calls the stored procedure InsertSalary and inserts records to Salary Table of the employees.