How to store columns values in TD stored procedure variable.

Database

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

2 REPLIES

Re: How to store columns values in TD stored procedure variable.

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

Re: How to store columns values in TD stored procedure 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)

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