Create Procedure with Dynamic Return Result in Teradata

access_time 4 months ago visibility89 comment 0

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. 


info Last modified by Raymond 4 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 466
thumb_up 0
access_time 10 months ago

In SQL Server, we can use TRUNCATE statement to clear all the records in a table and it usually performs better compared with DELETE statements as no transaction log for each individual row deletion. The syntax looks like the following: TRUNCATE TABLE { database_name.schema_name.table_name | ...

visibility 654
thumb_up 0
access_time 4 years ago

This post summarizes the tutorials I created in the past about Teradata. It is trying to help you gain the basic SQL skills with Teradata especially if you are a new beginner or you just move from SQL Server or Oracle. It also includes some guides about setting up your own Teradata server (latest Teradata Express version TDExpress16.10.00.03_Sles11_40GB.7z).

visibility 227
thumb_up 0
access_time 5 months ago

XML data type is commonly supported in Teradata. Together with native XML type, a number of XML functions are added to support extracting values from XML, shredding and publishing JSON, etc.  info The following code snippets use string literal to demonstrate the usage of these functions; you ...