Teradata SQL Stored Procedure Usage Guide

What Are Teradata SQL Stored Procedures?

Maybe the easiest way to describe Teradata Stored Procedures is in this way: Consider them as SQL enhanced with features traditional procedural languages like C, C++, C#, Pascal, etc., offer.

SQL is a set-orientated language, meaning you have to define your request, and Teradata will return a set of data matching your requirements. On the other hand, traditional programming languages like C++, etc., are procedural languages. The big difference is how you formulate which data you want to act upon or receive.

While in SQL, you have to define which data you want but not how this data has to be looked up. In a procedural language, you must specify your data’s access paths.

Teradata SQL stored procedures offer traditional SQL enhanced with the typical features most of the procedural languages offer:

• Iterations
• Condition Handling
• Error Handling
• Variables and Parameters

Teradata SQL Stored Procedures bridge the data stored in your RDBMS (relational database management system) and this additional procedural functionality.

You can operate on your data almost in the same way by using SQL within your Stored Procedure as you would in a BTEQ script or maybe in your SQL Assistant. You can issue, for example, an UPDATE statement to trigger a mass update on an individual table (in a SET manner).

Additionally, Stored Procedures allow you to iterate record by record over your data sets, i.e., in a procedural way. Iteration is achieved by using CURSOR functionality. In principle, a CURSOR is nothing but a pointer to a specific record within a Teradata Spool Space.

When To Use Teradata SQL Stored Procedures In Your Data Warehouse?

Stored Procedures should be considered an alternative to other options like BTEQ and Unix shell scripting whenever you need procedural features.

Stored Procedures encapsulate the programming logic on the Teradata Server, which means only the execution command has to be issued by the client. Still, the code is executed exclusively on the Teradata Server. Teradata will use heavy caching of SQL code executed within the Stored Procedure.

Please be aware that only the SQL statements issued directly to the Teradata RDBMS are leveraging the parallelism of the system. Don’t iterate over your data record by record (for example, doing an update) if executing one direct SQL statement can achieve the same result.

Stored Procedures offer excellent possibilities if used correctly. They should never be a substitute for tasks we can achieve with pure SQL. Especially people with a substantial procedural background often misuse Stored Procedures to overcome their lack of understanding of set-orientated languages like SQL.

I often have heard the argument that “Teradata SQL Stored Procedures are slow.” As I said, used correctly, I would consider them the best choice for adding procedural functionality to the RDBMS. Wrong implementations mainly cause slow Stored Procedures.

During the following weeks, I have planned to offer a complete series of articles related to Stored Procedures. If there is enough interest, I will provide a live webinar about SQL stored procedures in January 2015. I am currently in the preparation phase. More details will follow later.