Executre Show Procedure Dinamic

Database

Executre Show Procedure Dinamic

Hi , We some help with the issue , we can`t execute some SHOW statement saved in the table previously , add the script:

 

SyntaxEditor Code Snippet

REPLACE PROCEDURE 'SOME DB'.SEARCH_TEXT_IN_PL_IGN(
                                              --OUT _OutPutText    VARCHAR(200),                                                  IN  _TextToSearch VARCHAR(200), 
                                              IN  _DataBaseName VARCHAR(100),
                                              IN  _TableLike    VARCHAR(100),
                                              IN  _TableKind    CHAR(1)     
                                              )LMAIN:BEGIN
                DECLARE _SqlInsert      VARCHAR(10000);
                DECLARE _ShowItemName   VARCHAR(10000);
                DECLARE _ShowItemDetal  VARCHAR(10000);
                DECLARE _ShowItemAux1   VARCHAR(10000);
                DECLARE _ShowItemAux2   VARCHAR(10000);
                DECLARE _TableNameLike  VARCHAR(100);

        
         L1:BEGIN /**VALIDAMOS Y BORRAMOS LAS TABLAS DE EXISTIR*/
                    IF EXISTS(SELECT 1 FROM dbc.tables WHERE 1=1 AND TRIM(databasename) = TRIM('STAGEP') AND TRIM(TABLENAME) IN ('_AUX1_SEARCH_PL_IGN','_AUX2_SEARCH_PL_IGN')) THEN
                        DELETE FROM 'SOME DB.TABLE' ALL;
                        DELETE FROM 'SOME DB.TABLE' ALL;
                    END IF;
                 
                L2:BEGIN     /**INSERTAMOS LOS SHOW EN LA TABLA SEGUN LOS PARAMETROS DADOS*/
                     SET _SqlInsert = 'INSERT INTO     DB._AUX1_SEARCH_PL_IGN SELECT TRIM(T1.DatabaseName) || ''.'' || TRIM(T1.TABLENAME) AS itenName , TRIM(T1.DatabaseName) || ''.'' || TRIM(T1.TABLENAME) AS itemShow FROM DBC.Tables T1 WHERE DatabaseName = ''' || _DataBaseName || ''' AND TABLENAME LIKE  ''%' || _TableLike || '%'' AND  TableKind = ''' || _TableKind || ''';';
                     EXECUTE IMMEDIATE _SqlInsert;
                END L2;
                
                L3:BEGIN   /**EJECUTAMOS LOS SHOW EN LA TABLA SEGUN LOS PARAMETROS DADOS*/
                DECLARE c CURSOR FOR s;    
                    DECLARE _FIND_INTO_SP  CURSOR FOR
                        SELECT TRIM(itenName) AS itenName,TRIM(itemShow) AS itemShow FROM DB._AUX1_SEARCH_PL_IGN ORDER BY 1;
                        OPEN _FIND_INTO_SP;
                            WHILE(SQLCODE=0) DO
                               FETCH _FIND_INTO_SP INTO _ShowItemName,_ShowItemDetal;
                               
                               /**Constuimos los Shows Dinamicos para los Objetos*/
                               IF TRIM(_TableKind) = 'P' THEN
                                     SET _SqlInsert = 'SHOW PROCEDURE ' || _ShowItemDetal || ';';
                                 ELSE
                                     SET _SqlInsert = 'SHOW TABLE '     || _ShowItemDetal || ';';
                                END IF;
                       
                                PREPARE s FROM 'SHOW PROCEDURE ' || _ShowItemDetal || ';';
                                OPEN c;
                                    FETCH c INTO _ShowItemAux1;
                                CLOSE c;
                                
                                    IF (SQLSTATE = '02000') THEN
                                          LEAVE L3;
                                       END IF;
                                    
                               -- CALL STAGEP.EXECUTE_STATEMENT(_ShowItemDetal);                               INSERT INTO DB._AUX2_SEARCH_PL_IGN VALUES (_ShowItemName,'''' || _ShowItemAux1 || '''');
                               --SET _ShowItemAux1 = NULL;                            END WHILE;
                        CLOSE _FIND_INTO_SP;
                END L3;
        END L1;
END LMAIN;

ANY IDEAS FOR ISSUE?
ERROR : STATEMENT IS NOT SUPPORTED WITHIN A STORED PROCEDURE

I understanding the error , becasuse TERADATA can´t execute dinamic SQL statement , but How I can get the gold?

Thank a lot!