Dynamic Insert through Teradata Stored procedure

Question

I am trying to run Insert statement to load data into table using Teradata Stored procedure. Here I am trying to Input Table Name, Databasename as Parameter. My stored procedure compiled and ran well. But its not inserting any data into table. Could someone please help me with this. Below is the query I am using..

REPLACE PROCEDURE DB.TEST_SP
(
    IN SRC_DB_NM VARCHAR(30)
,   IN SRC_TBL_NM VARCHAR(30)
,   OUT MESSAGE VARCHAR(200)
)
DYNAMIC RESULT SETS 1
 
BEGIN
 
    DECLARE QUERY1 VARCHAR(200);
    DECLARE RESULT1 VARCHAR(200);
    DECLARE REC_COUNT INTEGER DEFAULT 0;
    DECLARE STATUS CHAR(10) DEFAULT '00000';  
    DECLARE C1 CURSOR FOR S1; 
     
    DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
    BEGIN
        SET STATUS = SQLCODE;
        IF(TRIM(STATUS)) = '3807' THEN
            SET MESSAGE = 'PASSED TABLE '||SRC_DB_NM||'.'||SRC_TBL_NM||' DOES NOT EXIST';
        ELSE
            SET MESSAGE = 'LOADED';
        END IF;
    END;
 
        BEGIN        
            SET QUERY1 ='INSERT INTO TABLE1 SELECT     ColX   , count(*)  from DB.table2  where Col in ( SELECT Col1 FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ' where ColY = 999 ) group by 1;' ;
          EXECUTE IMMEDIATE QUERY1;
        
            PREPARE S1 FROM QUERY1;
            OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
            FETCH C1 INTO RESULT1;
            SET MESSAGE = RESULT1;
        END;
         
END;