Hi, I am new to the TD store procedure. I have a requirement where i need to insert some records into a table.
I have a table TBL_WF_MAPPING, it has 2 fields WF_Name and Table_name.
In this table records are
I have another table LD_Stats
It has WF_Name,table_name,rowcount,Load_dt
An input parameter WF_Name is passed to procedure and it should return the table_name from TBL_WF_MAPPING table and store in some variable.
Once the table name is store in the variable, i need the count of that table.
I pass w_m_load_rbg_tailored_lending_loan as parameter to the procedure, so the procedure should return 3 records i.e. TIVYZ_MSTR_FACIL, TIVYZ_TRANSACTION and TIVYZ_LOAN_RSV and store these value in a variable.
Then i will do a select count(*) as rowcount from TIVYZ_MSTR_FACIL and store the value in another variable and then this value will be inserted in LD_Stats table.
similarly it will do a select count(*) as rowcount from TIVYZ_TRANSACTION table and value will be inserted in the LD_Stats table and so on
Please help me with some idea how should i go ahead with this approach
Your description is big :). If you want to store and use values in stored procedure, you can do this way.
Inside stored procedure, say you delcare a variable thus:
Declare V_Counter Integer;
then you can port values from select into this variable:
Thanks Raja for your reply, but seems you get me wrong. I need to execute the count(*) on a table which is actually the run time variable from the previous command. Based on my R&D i have prepared a folowing procedure..
REPLACE PROCEDURE WORK_DB.TBL_LOAD_STATS(IN WFNAME VARCHAR(60),IN DBNAME VARCHAR(30), IN LD_DATE DATE)
DECLARE DSQL VARCHAR(500);
DECLARE NEWCOUNT INTEGER;
DECLARE COUNT_TBL INTEGER;
DECLARE TBNAME VARCHAR(40);
DECLARE CNT_TABLE INT;
DECLARE CNT_SQL VARCHAR(200);
DECLARE VAR_CNT INT;
--getting the table name from WF_TABLE_MAPPING on the basis of :WFNAME
DECLARE CUR_TB_NM CURSOR FOR
SELECT TABLENAME FROM WORK_DB.WF_TABLE_MAPPING
WHERE WORKFLOW_NM = :WFNAME ORDER BY 1 ;
DECLARE CUR_CNT CURSOR FOR CNT_SQL;
SET NEWCOUNT = ACTIVITY_COUNT;
FETCH CUR_TB_NM INTO TBNAME;
-- once we get the table name from the above cursor, we need to execute the count(*) command on that table.
SET CNT_SQL= 'SELECT COUNT(*) FROM ' || TRIM(DBNAME)||'.'||TRIM(TBNAME) || ';' ;
PREPARE CURSOR_STATEMENT FROM CNT_SQL;
SET COUNT_TBL= ACTIVITY_COUNT;
FETCH CUR_CNT INTO VAR_CNT;
CALL dbc.sysexecsql('INSERT INTO WORK_DB.VOL_TEMP VALUES (TBNAME,NEWCOUNT,VAR_CNT,LD_DATE)');
SET COUNT_TBL = COUNT_TBL - 1;
UNTIL COUNT_TBL < 1
SET NEWCOUNT = NEWCOUNT - 1;
UNTIL NEWCOUNT = 0
I am able to compile the procedure, but while executing the command i am getting the below error
call WORK_DB.TBL_LOAD_STATS ('w_m_load_rbg_stg_hh_dtl','WORK_DB', '2013-10-03');
CALL Failed. 7682: TBL_LOAD_STATS:OPEN/EXECUTE/DEALLOCATE PREPARE attempted on a unprepared dynamic sql in the stored procedure (TBL_LOAD_STATS).
Please help me..