Create Procedure with Dynamic Return Result in Teradata

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.