Teradata SQL Stored Procedures: Adding Procedural Functionality to Your RDBMS

 

What Are Teradata SQL Stored Procedures?

Teradata Stored Procedures are essentially SQL with additional functionality found in traditional procedural languages such as C, C++, C#, and Pascal.

SQL is a set-based language where defining the request leads to Teradata returning a matching data set. In contrast, procedural languages like C++ utilize traditional programming methods. The primary difference lies in the approach to identifying the data to be acted upon or received.

In SQL, you define the desired data without specifying the retrieval method, whereas procedural languages require you to specify the access paths for your data.

Teradata SQL stored procedures combine conventional SQL with the advanced features found in most procedural languages.

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

Teradata SQL Stored Procedures connect the data stored in your RDBMS with extra procedural capabilities.

Operating on your data with SQL within a Stored Procedure is almost identical to utilizing a BTEQ script or SQL Assistant. For instance, you can execute an UPDATE statement to initiate a mass update on a single table (using SET).

Stored Procedures enable procedural iteration over datasets using the CURSOR functionality, which creates a pointer to a specific record in Teradata Spool Space.

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

Consider using Stored Procedures as a viable alternative to BTEQ and Unix shell scripting when requiring procedural capabilities.

Stored Procedures encapsulate programming logic on the Teradata Server, making it unnecessary for clients to execute anything but the command to run them. This code runs solely on the Teradata Server and benefits from substantial caching of executed SQL code.

Note that parallel processing is only utilized for SQL statements directly executed on the Teradata RDBMS. Avoid iterating through individual data records, such as during updates, when a single direct SQL statement can produce the same outcome.

Stored Procedures are highly beneficial when utilized appropriately, but they must not be used as replacements for tasks that can be accomplished with pure SQL. Those with a significant background in procedural programming may mistakenly rely on Stored Procedures to compensate for their lack of comprehension of set-oriented languages, such as SQL.

I’ve frequently heard the claim that Teradata SQL Stored Procedures are slow. However, when used appropriately, I believe they are the optimal solution for integrating procedural capabilities into the RDBMS. The primary cause of slow Stored Procedures is incorrect implementation.

Best practices for Teradata stored procedures

Most of the complaints about stored procedure performance come back to the same handful of habits.

• Keep the work set-based. Teradata parallelises the SQL statements a procedure issues, not the procedural logic around them. A loop that processes one row at a time gives all of that away, which is the single most common reason a procedure is called slow.
• Open a cursor only when the logic genuinely cannot be expressed as a set operation — and know what it costs when you do: the cursor mistakes that quietly serialise a procedure.
• Declare your error handling. A procedure without a handler fails in whatever way the calling session happens to be configured for. See handling errors and exceptions in a procedure.
• Prefer static SQL where the statement is known in advance. Dynamic SQL is occasionally the only option, but it gives up compile-time checking and is harder to read months later: dynamic against static SQL, and what each costs.
• Watch variable scope in nested blocks. A variable declared in an inner BEGIN...END hides the outer one of the same name, and the resulting bug is silent: how variable scope works inside nested compound statements.

Used this way, a stored procedure is not slow. Used as a loop around single-row SQL, it is, and no amount of tuning on the statements inside will fix that.