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.
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.
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.