CALL an SP from within another SP

Database
Highlighted
Enthusiast

CALL an SP from within another SP

 HI,

I have a working Stored Procedure (SP) that creates & Inserts records into a table on a Database.

In case I want to run it over & over & not have to drop the tables manually, I also created an SP that Drops this particular table.
Hoping someone can advise any rules about adding an SP to another SP??

I've included both below;

The error I'm getting is:

 

0004 CALL datalab_a.sb_sp_a_drop_if_exists;
**** 13:29:53 FDL4800 Invalid FastLoad statement

Here's the main SP with the CALL statement inside:

SESSIONS 2;
LOGMECH LDAP;
LOGON TDDB001.xxxxxxxxx/USER/Pwd

CALL datalab_a.sb_sp_a_drop_if_exists
; --- ADDING THIS SP CAUSES THE ERROR CREATE MULTISET TABLE datalab_a.AA_AUS_STATES , NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( STATE_ID VARCHAR(2) CHARACTER SET Latin NOT CaseSpecific, STATE VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific, ABBR VARCHAR(3) CHARACTER SET Latin NOT CaseSpecific, CAPITAL VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific, LARGEST_CITY VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific ) PRIMARY INDEX (STATE_ID); SET RECORD VARTEXT ","; RECORD 2; DEFINE STATE_ID (VARCHAR(2)) , STATE (VARCHAR(30)), ABBR (VARCHAR(3)), CAPITAL (VARCHAR(30)), LARGEST_CITY (VARCHAR(30)) FILE = D:\Users\Desktop\AA_AUS_STATES.CSV; BEGIN LOADING datalab_a.AA_AUS_STATES ERRORFILES datalab_a.AA_Excl_ERR1, datalab_a.AA_Excl_ERR2; INSERT INTO datalab_a.AA_AUS_STATES ( STATE_ID, STATE, ABBR, CAPITAL, LARGEST_CITY ) VALUES ( :STATE_ID, :STATE, :ABBR, :CAPITAL, :LARGEST_CITY ); END LOADING; LOGOFF;

And finally, here's the SP that's being Called:

CREATE PROCEDURE datalab_a.sb_sp_a_drop_if_exists

BEGIN

			IF EXISTS
						(
						SELECT 1 
						FROM datalab_a.AA_AUS_STATES
						) 
			THEN

			DROP TABLE datalab_a.AA_AUS_STATES;

			END IF;

END;

Accepted Solutions
Teradata Employee

Re: CALL an SP from within another SP

It seems that rather than a "main SP" you have a FastLoad script. FastLoad does not support a SP CALL statement, only CREATE TABLE, DROP TABLE, DELETE, DATABASE, SET QUERY_BAND FOR SESSION (and of course the INSERT to actually load the table) are allowed.

 

You can use BTEQ to run the SP followed by FastLoad. Or consider switching to TPT LOAD - then you can have a "DDL Operator" step that calls the SP and a "LOAD" step within the same job.

1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: CALL an SP from within another SP

It seems that rather than a "main SP" you have a FastLoad script. FastLoad does not support a SP CALL statement, only CREATE TABLE, DROP TABLE, DELETE, DATABASE, SET QUERY_BAND FOR SESSION (and of course the INSERT to actually load the table) are allowed.

 

You can use BTEQ to run the SP followed by FastLoad. Or consider switching to TPT LOAD - then you can have a "DDL Operator" step that calls the SP and a "LOAD" step within the same job.

Enthusiast

Re: CALL an SP from within another SP

Thanks Fred,

 

You're correct, it is a FastLoad script, as I now have a need to load multiple tables, I'll try the TPT option, Thanks