All Forums Analytics
NMadson-3173 44 posts Joined 09/06
27 Oct 2006
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 executionI 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_01END;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.

NMadson-3173 44 posts Joined 09/06
30 Oct 2006

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

Fred 1096 posts Joined 08/04
31 Oct 2006

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.

NMadson-3173 44 posts Joined 09/06
31 Oct 2006

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

Fred 1096 posts Joined 08/04
31 Oct 2006

Clearest statement I see:Chapter 1: SQL CursorsTypes of CursorsCursors can be classified in several different ways:1 Dynamic2 Macro3 Request4 Selection5 Stored procedure6 Positioned (updatable)7 Non-positionedImportant: 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.

idontknow 25 posts Joined 07/07
15 Jul 2007

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 LuckRob

s@ir@m 35 posts Joined 05/13
24 Dec 2013

Hi ,
 
can i call a stored procedure with in the macro ?
 
ratnam

dnoeth 4628 posts Joined 11/04
24 Dec 2013

Hi Ratnam,
yes, if it's the only statement.
Of course you could have tried that easily on your own :-)

Dieter

s@ir@m 35 posts Joined 05/13
24 Dec 2013

Thank u Dieter,
 
1.and as well as macro inside another macro?
2.we are using DDL in Macro?
 
Ratnam

s@ir@m 35 posts Joined 05/13
06 Jan 2014

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

Raja_KT 1246 posts Joined 07/09
06 Jan 2014

You mean only query column thus:
select query from (select id,query from EDW01_D_IPA_DV_STG_N_01.raja_test where id=1)a;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

s@ir@m 35 posts Joined 05/13
08 Jan 2014

ok raja,
 
suppose id=1  column condition is true than call the emp table ,
id=2 i struee than call the dept table how ?

Raja_KT 1246 posts Joined 07/09
08 Jan 2014

 

Hi Ratnam,

From  (select id,query from ......raja_test where id=1) I get all the fields with id=1. But from outside I select only the query, because I want only the query. I suggest you test it :). I think your requirement is to get the query alone.Please let me know.

 

like 

sel * from emp

sel * from dept

sel * from customer

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

s@ir@m 35 posts Joined 05/13
08 Jan 2014

hi raja,
 
we are developed some bteq script like error handling
based upon status table we get the query table to target table loading data can loading .
 
Thanks
Ratnam 
 

dnoeth 4628 posts Joined 11/04
08 Jan 2014

Hi Ratnam,
you want to execute the SELECT found in query in BTEQ?

.EXPORT FILE dynSQL.txt;
SELECT query (TITLE '') FROM aquery_Table WHERE id = 1;
.EXPORT RESET
.RUN FILE dynSQL.txt;

 

Dieter

You must sign in to leave a comment.