Javatpoint Logo
Javatpoint Logo

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.


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.


Following is the syntax of the CREATE PROCEDURE statement.


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.

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.


Following is the generic syntax of the CALL statement.


The below example calls the stored procedure InsertSalary and inserts records to 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
202005 20,000 1,000 19,000

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA