Create Procedure with Dynamic Return Result in Teradata
insights Stats
A procedure contains a number of SQL statements that will be executed in sequence in Teradata. Procedures can be used to return result sets to the invoking clients. In many other databases, it is very easy to return set records to the client in a procedure as SELECT statement can be directly used. In Teradata, it is slightly different as only SELECT with INTO can be directly used.
Procedure in SQL Server
The following is an example procedure in SQL Server:
CREATE PROCEDURE HumanResources.uspGetAllEmployees AS SET NOCOUNT ON; SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment; GO
It returns the results from the SELECT query.
Running similar query like the following in Teradata will raise on error.
REPLACE PROCEDURE TestDb.TesProcWithSelect() DYNAMIC RESULT SETS 1 BEGIN SELECT * FROM TestDb.FASTLOAD_CSV; END;
Error message:
REPLACE PROCEDURE Failed. [5526] SPL1045:E(L4), Invalid or missing INTO clause.
Procedure in Teradata
In Teradata, result set can be generated using any of the following statements:
- SELECT
- HELP TABLE
- HELP VIEW
- HELP MACRO
- SHOW TABLE
- SHOW VIEW
- SHOW MACRO
- COMMENT
To return result sets, cursor needs to be declared.
The following code snippet creates a procedure that returns one result set:
REPLACE PROCEDURE TestDb.TesProcWithSelect() DYNAMIC RESULT SETS 1 BEGIN DECLARE cur1 CURSOR WITH RETURN ONLY FOR SELECT * FROM TestDb.FASTLOAD_CSV; OPEN cur1; END;
When calling the procedure using the following statement, all records in table TestDb.FASTLOAD_CSV will be returned:
CALL TestDb.TesProcWithSelect();
Return multiple result sets
Multiple result sets can be returned to the client. To do that, procedure needs to defined with 'DYNAMIC RESULT SETS N' where N is the number of result sets.
For example, the following code snippet returns two result sets:
REPLACE PROCEDURE TestDb.TesProcWithSelect() DYNAMIC RESULT SETS 2 BEGIN DECLARE cur1 CURSOR WITH RETURN ONLY FOR SELECT * FROM TestDb.FASTLOAD_CSV; DECLARE cur2 CURSOR WITH RETURN ONLY FOR SELECT * FROM TestDb.FASTLOAD_CSV2; OPEN cur1; OPEN cur2; END;
Return result with dynamic
If you prefer dynamic SQL, we just need to define a dynamic cursor object:
REPLACE PROCEDURE TestDb.TesProcWithSelect (IN SQL_STR VARCHAR(4000), IN ID INT) DYNAMIC RESULT SETS 1 BEGIN DECLARE cur1 CURSOR WITH RETURN ONLY FOR Statement1; PREPARE Statement1 FROM SQL_STR; OPEN cur1 USING ID; END;
The above procedure accepts two input parameters:
- SQL_STR: SQL statements to be called.
- ID: variables that will be used in the SQL statement.
The following example passes one statement and a filter value for column ID:
CALL TestDb.TesProcWithSelect('SEL * FROM TestDb.FASTLOAD_CSV WHERE ID = ?', 1);
The output will be the same as running the following SQL:
SEL * FROM TestDb.FASTLOAD_CSV WHERE ID = 1;
Hopefully you learnt something from this article.