All Forums Database
khubeb786 2 posts Joined 10/13
03 Oct 2013
How to store columns values in TD stored procedure variable.

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
WF_Name,Table_name.
w_m_load_rbg_tailored_lending_loan,TIVYZ_MSTR_FACIL
w_m_load_rbg_tailored_lending_loan,TIVYZ_TRANSACTION
w_m_load_rbg_tailored_lending_loan,TIVYZ_LOAN_RSV
w_m_load_laser_tl_fee_totals,TTL_LASER_ACCT_FEE
w_m_load_rbg_swap_risk,TSWAP_RISK

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.
For eg.
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

Raja_KT 1246 posts Joined 07/09
04 Oct 2013

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:
 

SELECT COUNT(*) INTO :V_Counter

 

from (

 

SELECT id1 from table1

 

UNION

 

SELECT DISTINCT id2 from table

 

) as T

 

Hope this is what you want.

 

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.

khubeb786 2 posts Joined 10/13
07 Oct 2013

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)
BEGIN
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;
OPEN CUR_TB_NM;
SET NEWCOUNT = ACTIVITY_COUNT;
REPEAT
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;
 
 OPEN CUR_CNT;
 SET COUNT_TBL= ACTIVITY_COUNT;
 REPEAT
 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
 END REPEAT;
 CLOSE CUR_CNT;

SET NEWCOUNT = NEWCOUNT - 1;
UNTIL NEWCOUNT = 0
END REPEAT;
CLOSE CUR_TB_NM;
END 
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..

You must sign in to leave a comment.