DECLARE CURSOR (Macro Form) - Calling Macro Inside SP using CURSORS

General
Enthusiast

DECLARE CURSOR (Macro Form) - Calling Macro Inside SP using CURSORS

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

1 REPLY
Enthusiast

Re: DECLARE CURSOR (Macro Form) - Calling Macro Inside SP using CURSORS

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.