Dynamic SQL - Stored Procedure

Database
Enthusiast

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!

3 REPLIES
Enthusiast

Re: Dynamic SQL - Stored Procedure

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

Junior Contributor

Re: Dynamic SQL - Stored Procedure

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

Enthusiast

Re: Dynamic SQL - Stored Procedure

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