Teradata SQL Stored Procedures: Adding Procedural Functionality to Your RDBMS
Teradata SQL Stored Procedures enhance traditional SQL with procedural language features like iterations, condition and error handling, and variables. Learn more here.
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.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
Teradata SQL stored procedures combine conventional SQL with the advanced features found in most procedural languages.
Get the next issue by email.
- 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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.