In the Teradata documentation "SQL Reference: Stored Procedures and Embedded SQL" I see the following:
Rules for Using DECLARE CURSOR (Macro Form) The following rules apply to the Macro DECLARE CURSOR statement: • When the cursor is opened, the macro is performed. Once the macro has been performed, the results of macro execution
I interpreted this to mean that I can use the DECLARE CURSOR to execute the macro from within a stored procedure.
However when I attempt to create the stored procedure
Create Procedure test_call_01 (IN StartDate DATE) BEGIN DECLARE cur_01 CURSOR FOR m_test_01 END;
I get error messages below. The first about a SELECT statement implies that I can't use a macro but have to spell out a complete SELECT statement.
A further bit of information: my macro m_test_01 creates a volatile table using a select statement. Is the problem that I can't use a CREATE VOLATILE TALBE statement within a macro that is called from within a Stored Procedure?
SPL1007:E(L4), Unexpected text 'm_test_p1' in place of cursor SELECT statement.
SPL1007:E(L4), Unexpected text 'END' in place of cursor SELECT statement.
SPL1048:E(L4), Unexpected text ';' in place of SPL statement.
SPL1027:E(L4), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the end of the request.'.
DECLARE CURSOR (Macro Form) documentation detail does say it can be used within a Stored Procedure, but I think that is a documentation error. The SP section of the documentation is fairly clear that only a restricted form of SELECT cursor is allowed.
Cursors can be classified in several different ways: 1 Dynamic 2 Macro 3 Request 4 Selection 5 Stored procedure 6 Positioned (updatable) 7 Non-positioned Important: The first five types refer to ways a cursor can be declared in a DECLARE CURSOR statement. Of these, only stored procedure-type cursors are supported in stored procedures. The last two types refer to ways a cursor can be used to manipulate data. Both are supported in embedded SQL and stored procedures.
I went to an instrutor led course regarding the use of SP's last week and asked precisely this question. The response goes as follows...
'You cannot call a macro from within a Stored Procedure, this is due to Macros being designed to allow the return of large amounts of rows as a recordset, and SP's are designed to operate sequentially and not in parallel, the allowance of SP's to call macros would then cause large processing overheads which would not be desirable.'
Obviously you can transpose the macro code within the SP and then use the dynamic cursor to p**** the returned recordset if you wish.