Call a macro from a stored procedure?

Analytics
Enthusiast

Call a macro from a stored procedure?

Can one call a macro from a stored procedure?

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.'.

N.
14 REPLIES
Enthusiast

Re: Call a macro from a stored procedure?

Well, I see in Teradata magazine that EXECUTE doesn't work to call a macro from a stored procedure. However, the FAQ item doesn't give any further example of how to use this syntax.

"The EXECUTE macro statement is not supported by stored procedures. Teradata SQL macros can be used as part of a DECLARE CURSOR (macro form) within a stored procedure. "
Teradata Employee

Re: Call a macro from a stored procedure?

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.
Enthusiast

Re: Call a macro from a stored procedure?

Can you point me to the page number in the Teradata documentation? I'm not seeing the text you referenced.

Thx,
N.
Teradata Employee

Re: Call a macro from a stored procedure?

Clearest statement I see:

Chapter 1: SQL Cursors

Types of Cursors

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.
Enthusiast

Re: Call a macro from a stored procedure?

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.

Good Luck

Rob
s_1
Enthusiast

Re: Call a macro from a stored procedure?

Hi ,

can i call a stored procedure with in the macro ?

ratnam

Senior Apprentice

Re: Call a macro from a stored procedure?

Hi Ratnam,

yes, if it's the only statement.

Of course you could have tried that easily on your own :-)

s_1
Enthusiast

Re: Call a macro from a stored procedure?

Thank u Dieter,

1.and as well as macro inside another macro?

2.we are using DDL in Macro?

Ratnam

s_1
Enthusiast

Re: Call a macro from a stored procedure?

hi,

 

i have aquery_Table

 

id           date                       query

1          12/01/2014               sel * from emp

2          07/01/2014               sel * from dept

3          06/01/2014               sel * from customer

 

 

how to get query column  if id is comparing and query column query  is it possible ?.

 

Regards

ratnam