Use Cursors in Stored Procedure in Teradata to return Resultset

In the previous post, we learned how to CREATE a Stored Procedure in Teradata.

In this post, we will see how to return resultset by using CURSORs in STORED PROCEDURES. So in the below mentioned stored procedure example, we will insert some data into the table and will return the resultset. Let's create one table first which we will use in this example.

CREATE MULTISET VOLATILE TABLE US_STATES , NO JOURNAL, NO LOG,NO FALLBACK
 (
 STATE_NAME VARCHAR(13),
 STATE_ABBR CHAR(2),
 STATE_CAPITAL VARCHAR(13),
 STATE_CITY VARCHAR(13)
 )
 PRIMARY INDEX(STATE_NAME)
 ON COMMIT PRESERVE ROWS
 ;

Now let's create one Stored Procedure which will INSERT some records into this table and will then return the resultset having all the records from this table.

REPLACE PROCEDURE US_STATE_2 ()
 DYNAMIC RESULT SETS 1
 BEGIN
 DECLARE US_STATES_DETAILS CURSOR WITH RETURN ONLY FOR SEL * FROM US_STATES;
 INSERT INTO US_STATES VALUES('Alabama','AL','Montgomery','Birmingham');
 INSERT INTO US_STATES VALUES('Alaska','AK','Juneau','Anchorage');
 INSERT INTO US_STATES VALUES('Arizona','AZ','Phoenix','Phoenix');
 INSERT INTO US_STATES VALUES('Arkansas','AR','LittleRock','LittleRock');
 INSERT INTO US_STATES VALUES('California','CA','Sacramento','LosAngeles');
 OPEN US_STATES_DETAILS;
 END;
 

Create this procedure and it should complete successfully.
To execute this procedure, CALL US_STATE_2();

Few points to remember:
DYNAMIC RESULT SETS: It is used to specify number of result sets that will be returned to the caller/client running the Stored Procedures.
CURSOR: CURSOR is data structure which holds result set of the query in Stored Procedures.
DECLARE US_STATES_DETAILS CURSOR WITH RETURN ONLY FOR SEL * FROM US_STATES: This statement DECLARES the cursor with name US_STATES_DETAILS and returns the result set to client/caller for the query SEL * FROM US_STATES.
OPEN US_STATES_DETAILS: This command OPENS the CURSOR to execute the SELECT statement.

Kudos , after this lesson you know how to CREATE a STORED Procedure in Teradata and Return the resultset to the caller.