You can use the DYNAMIC RESULT SETS clause in the CREATE/REPLACE PROCEDURE statement to return up to 15 result sets to the caller (an external stored procedure) or client (an application such as BTEQ) of the stored procedure. The stored procedure returns result sets in the form of a multistatement response spool.
The stored procedure assumes zero result sets if the clause is absent. The stored procedure may return no result sets, or fewer result sets than specified by the DYNAMIC RESULT SETS clause.
This clause is optional. Do not use it if you do not want the stored procedure to return result sets.
Creating a Stored Procedure and Returning Result Sets to the Caller or Client
- Use the DYNAMIC RESULT SETS clause in the CREATE/REPLACE PROCEDURE statement to specify the number of result sets the stored procedure returns. For example, the following statement defines a stored procedure that returns one result set:
CREATE PROCEDURE sp1 (IN SqlStr VARCHAR(50), IN a INT) DYNAMIC RESULT SETS 1
- Use a DECLARE CURSOR statement to declare a result set cursor for each result set the stored procedure returns.
- Specify WITH RETURN ONLY TO CALLER or WITH RETURN ONLY for the stored procedure to return the result set(s) only to the caller of the target procedure. If the client called the procedure, the stored procedure returns the result set(s) to the client application. If a stored procedure called the target procedure, the stored procedure returns the result set(s) to the calling procedure, which can also be an external stored procedure that allows SQL.
- Specify WITH RETURN TO CALLER or WITH RETURN for the stored procedure to return the result set(s) to both the caller of the target procedure and to the target procedure (the procedure that opened the cursor).
- Specify WITH RETURN TO CLIENT for the stored procedure to return the result set(s) to both the client (application such as BTEQ) and to the target procedure (the procedure that opened the cursor).
- Specify WITH RETURN ONLY TO CLIENT for the stored procedure to return the result set(s) only to the application.
- If the SELECT statement that produces the result set is static, include it as the SELECT portion of the DECLARE CURSOR statement. For example:
DECLARE cur1 CURSOR WITH RETURN ONLY FOR SELECT * FROM m1;
- To use the dynamic form of the DECLARE CURSOR statement to return a result set, use a statement name instead of SELECT. For example:
DECLARE c1 CURSOR WITH RETURN ONLY FOR s1
- Use a PREPARE statement for each dynamic form of DECLARE CURSOR to prepare the statement name that was specified. For example:
PREPARE s1 FROM SqlStr;
- Use an OPEN statement to open each result set cursor to execute the static or dynamic SELECT statement. If the dynamic select statement uses parameter markers (the question mark character), specify a USING clause to identify the variables to use as input. For example:
OPEN c1 USING a;
Note that you must specify the same number of variables the USING clause as the number of parameter markers.
- Use the FETCH statement to position the result set cursor to read from the result set.
- Leave the result set cursors open to return the result sets to the caller or client. If the stored procedure closes the result set cursor, the result set is deleted and not returned. The result sets are returned in the order they were opened.
Example: Static Form of the DECLARE CURSOR Statement
Following is an example of using the DYNAMIC RESULT SETS clause and the static form of the DECLARE CURSOR statement to create a stored procedure that returns result sets.
CREATE PROCEDURE Sample_p (INOUT c INTEGER) DYNAMIC RESULT SETS 2 BEGIN DECLARE cur1 CURSOR WITH RETURN ONLY FOR SELECT * FROM m1; DECLARE cur2 CURSOR WITH RETURN ONLY FOR SELECT * FROM m2 WHERE m2.a > c; SET c = c +1; OPEN cur1; OPEN cur2; END;
Following is an example of how BTEQ (the client) would read the result sets for the sample stored procedure:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
CALL sample_p(1);
*** Procedure has been executed.
*** Warning: 3212 The stored procedure returned one or more result sets.
*** Total elapsed time was 1 second.
1
-----------
2
*** Procedure dynamic result set. One row found. 2 columns returned.
*** Starting Row Number: 1
*** Database Name: FSK
*** Procedure Name: SAMPLE_P
a b
----------- ----------------------
1 2.00000000000000E 000
*** Procedure dynamic result set. One row found. 2 columns returned.
*** Starting Row Number: 1
*** Database Name: FSK
*** Procedure Name: SAMPLE_P
a b
----------- ----------------------
2 4.00000000000000E 000
Example: Dynamic Form of the DECLARE Statement
In the following example, the DECLARE CURSOR statement includes statement name s1. The PREPARE statement references s1 to prepare the dynamic select statement contained in SqlStr of the CREATE PROCEDURE statement. The OPEN statement opens the result set cursor c1 specified in the DECLARE CURSOR statement, USING the parameter a specified in the CREATE PROCEDURE statement.
CREATE PROCEDURE sp1 (IN SqlStr VARCHAR(50), IN a INT) DYNAMIC RESULT SETS 1 BEGIN DECLARE c1 CURSOR WITH RETURN ONLY FOR s1; PREPARE s1 FROM SqlStr; OPEN c1 USING a; END;
Following is an example of a dynamic select statement you might input in BTEQ. Note that the CALL statement includes the same number of parameter markers as the USING clause variables.
CALL sp1(‘sel * from tab1 where a = ? order by 1;’,1);
Related Information
- Reading result sets in an external stored procedure, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.
- DECLARE CURSOR statement, see DECLARE CURSOR.