Create Procedure with Dynamic Return Result in Teradata

access_time 2 months ago visibility23 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 at 2 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

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer python local_offer python-database

visibility 2122
thumb_up 1
access_time 7 months ago

Pandas is commonly used by Python users to perform data operations. In many scenarios, the results need to be saved to a storage like Teradata. This article shows you how to do that easily using JayDeBeApi or  sqlalchemy-teradata   package.  JayDeBeApi package and Teradata JDBC ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 30
thumb_up 0
access_time 2 months ago

Teradata RPAD function is used to add repeated characters at the end of a string to increase the string to a specified length. It can be used to add trailing space or zeros to a string. RPAD(source_string, length, fill_string) Returns the source_string padded to the right with the characters in ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 283
thumb_up 0
access_time 8 months ago

In SQL Server, TRY_CAST function returns a value cast to a specified data type is the cast is successful or null is the cast is not successful.  In Teradata, the equivalent function is TRYCAST. -- return int 1030 SELECT TRYCAST('01030' AS int); -- return 2019-01-01 as it is a valid date ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS