Teradata SQL Stored Procedures – 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. are offering.
SQL is a set-orientated language, meaning you have to define your request and a set of data matching your requirements will be returned. On the other hand, traditional programming languages like C++, etc. are procedural languages. The big difference is the way you formulate which data you want to act upon or receive.
While in SQL you just have to define which data you want but not how this data has to be looked up, in a procedural language you have to specify exactly the access paths to your data.
Teradata SQL stored procedures offer traditional SQL enhanced with the typical features most of the procedural languages offer:
- Condition Handling
- Error Handling
- Variables and Parameters
Teradata SQL Stored Procedures are the bridge between 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 like you would do it in a BTEQ script or maybe in your SQL Assistant. You can issue for example a UPDATE statement to trigger a mass update on an individual table (in a SET operation manner).
Additionally Stored Procedures offer you the possibility 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 else than a pointer to a certain record within a Teradata Spool Space.
Technically spoken, whenever you write
When to use Teradata SQL Stored Procedures in your Data Warehouse?
Whenever you need procedural features, Stored Procedures should be considered as an alternative to other options like BTEQ together with Unix shell scripting.
Stored Procedures are encapsulating the programming logic on the Teradata Server which means only the execution command has to be issued by the client, but the code is executed exclusively on the Teradata Server. Heavy caching of SQL code executed within the Stored Procedure will be used.
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 the same result can be achieved by executing one direct SQL statement.
Stored Procedures are offering excellent possibilities if used in the correct way. They never should be a substitute for tasks which can be achieved with pure SQL. Especially people with a substantial procedural background often tend to misuse Stored Procedures for overcoming their lack of understanding set-orientated languages like SQL.
I often have heard the argument that “Teradata SQL Stored Procedures are slow.” As I said, used in a proper way I would consider them as the best choice for adding procedural functionality to the RDBMS. Slow Stored Procedures mostly are caused by wrong implementations.
During the next weeks, I have planned to offer a complete series of articles related to Stored Procedures. If there is enough interest, I will offer a live webinar about SQL stored procedures in January 2015. I am currently in the preparation phase, more details will follow later.