All Forums Database
raestadilla 5 posts Joined 08/13
05 Aug 2013
Dynamic SQL - Stored Procedure

Hi!
 
My goal is to create an SP which gets the tablename and it's row count then insert it in another table. This has to be dynamic since row counts should be done for more than 1 table.
 
Here's my code:
 
REPLACE PROCEDURE DB.TEST_PROC()
BEGIN
                DECLARE TBLNAME VARCHAR(1000);
                DECLARE SQL_INS VARCHAR(1000);
                DECLARE TCOUNT VARCHAR(1000);
               
                FOR test AS t2
                                CURSOR FOR
                                                SELECT TD_TBL_NM FROM DB_S.TBL_LIST
                DO
               
                SET TBLNAME = 'DB_S.' || test.TD_TBL_NM;
                SET TCOUNT = 'SELECT COUNT(*) FROM ' || TBLNAME || ';';
                SET SQL_INS = 'UPDATE ICDW_FL_EDW_W.TBL_MSTR_MLA SET WORK_COUNT = ' || TCOUNT || ' WHERE DB_S.TBL_LIT.TD_TBL_NM = ' || test.TD_TBL_NM;
 
                CALL DBC.SysExecSQL(SQL_INS);
END FOR;
END;
 
Thanks for the help!

raestadilla 5 posts Joined 08/13
06 Aug 2013

By the way, this code is not working. :)

dnoeth 4628 posts Joined 11/04
07 Aug 2013

You're right, your code is not working.
And the error is easy to spot :-)
You expect us to fix it without even telling the error message?
 
Dieter

Dieter

TD_Raj 50 posts Joined 05/10
07 Aug 2013

Hi 
Try something like this:
 

DECLARE T2 CURSOR FOR
SELECT TD_TBL_NM FROM DB_S.TBL_LIST
OPEN T2;

LABEL1 : LOOP

FETCH T2 INTO TABLE_NAME;  /* TABLE_NAME is a variable here */

/* 
DO SOME DML OPERATIONS 
*/

END LOOP LABEL1;

CLOSE T2;

 

--
Raj

You must sign in to leave a comment.