15.00 - 15.10 - Using SQL Stored Procedures - Teradata Database

Teradata Database Introduction to Teradata

Product
Teradata Database
Release Number
15.00
15.10
Content Type
User Guide
Publication ID
B035-1091-151K
Language
English (United States)
Last Update
2018-09-25

Using SQL stored procedures, you can build large and complex database applications. In addition to a set of SQL control statements and condition handling statements, an SQL stored procedure can contain the following:

  • Multiple input and output parameters.
  • Local variables and cursors.
  • SQL DDL, DCL, and DML statements, including dynamic SQL, with a few exceptions.
  • Dynamic SQL is a method of invoking an SQL statement by creating and submitting it at runtime from within a stored procedure.

    Applications based on SQL stored procedures provide the following benefits. They:

  • Reduce network traffic in the client-server environment because stored procedures reside and execute on the server.
  • Allow encapsulation and enforcement of business rules on the server, contributing to improved application maintenance.
  • Provide better transaction control.
  • Provide better security. The data access clause of an SQL stored procedure can restrict access to the database.
  • Provide better security by granting the user access to the procedures rather than to the data tables.
  • Provide an exception handling mechanism to handle the runtime conditions generated by the application.
  • Allow all the SQL and SQL control statements embedded in an SQL stored procedure to be executed by submitting one CALL statement. Nested CALL statements further extend the versatility.