All Forums General
venugopal2705 5 posts Joined 08/11
11 Dec 2011
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

barryhull 31 posts Joined 05/11
20 Dec 2011

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.

You must sign in to leave a comment.