Hi All,

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()

BEGIN

DECLARE Ex CURSOR FOR EXEC NewEmp;

END;

Error :

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

Purpose
Defines and assigns a name to a macro cursor.
Invocation
Nonexecutable preprocessor and stored procedure declaration.
Stored procedures and embedded SQL only.
Syntax
where:
ANSI Compliance
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
None.

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
request cursor.
• None of the statements in the specified macro can be preprocessor or stored procedure
declaratives.
• The macro cannot include any of the following SQL statements:
Example
Structure the Macro DECLARE CURSOR statement as follows:
DECLARE Ex CURSOR FOR EXEC NewEmp

Regards,

Vg