CODEWITHSUNDEEP
sqlloopsstored-proceduresteradata
Nishant
Nishant

Reputation: 74

Passing parameters in Teradata stored procedure (with Looping)

I am from reporting background and first time building cubes from Teradata. I am trying to fill my fact table by one master SP. Master SP will call child SP. Child SP will be like this- GetData(201704). I have to call this SP for last 13 month(configurable). I have created a table for getting months with rownum which will give me below result.

Month RowNum

201704 1

201703 2

201702 3

Please find logic for master SP below. If someone can help me with syntax.

CREATE MasterSP(MonthCount)

Begin

Declare Count integer;

Declare Period integer;

SET Count=MonthCount; --13 months

loopstart IF(I<13)

SELECT Month From TimePeriod WHERE RowNum=Count

SET GetData(Month)

Count=Count+1

loop end

END

Upvotes: 1

Views: 4361

Answers (1)

Nishant
Nishant

Reputation: 74

I have managed to create below SP and working fine.

Replace PROCEDURE MYDB.MasterLoad( MntCnt Integer)
BEGIN
Declare MonthCount integer;
Declare IniCount integer;
Declare PeriodNum Integer;

SET IniCount =1;
SET MonthCount = MntCnt;


WHILE IniCount <= MonthCount DO
   BEGIN 

SELECT AccountingReportMonthPeriodNum into PeriodNum  FROM MYDB.Monthly_Dim_TimePeriod WHERE Rownum=IniCount; 

CAll MYDB.MthCustFact_Test(PeriodNum) ;   


SET IniCount = IniCount+1;

   END;
END WHILE;



END;

Upvotes: 1

Related Questions