In Teradata document, SQL Reference : Stored Procedures and Embedded SQL :Release V2R6.1
I have read something about calling MACRO inside a procedure and declaring the results in Cursor
(The content is given below) When I tried the same thing in procedure, I have got a error saying it is Invalid.
Am I doing something wrong here? or Teradata Documentation is wrong?
Please try this for me let me know the results. I need to use macro in my SP.
REPLACE PROCEDURE sp_venumac1()
DECLARE Ex CURSOR FOR EXEC NewEmp;
SPL1007:E(L4), Unexpected text 'EXEC' in place of cursor SELECT statement.
SPL1007:E(L4), Unexpected text 'NewEmp' in place of cursor SELECT statement.
CONTENT OF THE DOCUMENT
Defines and assigns a name to a macro cursor.Invocation
Nonexecutable preprocessor and stored procedure declaration.Stored procedures and embedded SQL only.Syntaxwhere:
The macro form of DECLARE CURSOR is a Teradata extension to the ANSI SQL-2003
standard because macros are not defined in ANSI SQL.Authorization
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 are accessed by the application program as the results of a
• None of the statements in the specified macro can be preprocessor or stored procedure
• The macro cannot include any of the following SQL statements:
Structure the Macro DECLARE CURSOR statement as follows:DECLARE Ex CURSOR FOR EXEC NewEmp
I have the release 12 manuals for stored procedures and under "Restrictions for stored procedures", it says that you cannot execute a macro from within a stored procedure, so I believe that the part of the manual you are looking at is dealing with an application program executing a macro.