What Are Teradata SQL Stored Procedures?
Teradata Stored Procedures are essentially SQL with added 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.
As far as I know just from BTEQ, not from the load utilities.
Hi, what are the possible ways of executing/calling a stored procedure? Can we call them from all Teradata utilities like BTEQ, Multiload, Fastload, and TPT?
I wish, I had this article to show one of my clients about a year ago. I suggested cursor-based processing in the stored procedure, where each record needs to be processed for several conditions. I received a strong opposition on the ground that set-based processing is more efficient than cursor-based processing.
Instead of one cursor-based process, they had to code 10plus set-based processes to get a smaller subset in each process. I wanted to demonstrate the efficiency of cursor-based processing on a small sample with a pre-compiled stored procedure, but I was not allowed any time for testing.
I wish, Teradata educate their clients more, so they could make the appropriate choices.