Stored Procedure with system tables
<p>I have two different system tables in my database: <code>Systables</code> (which stores all tables of the database) and <code>Syscolumns</code> (which stores all columns of the tables of the database).</p>

<ul>
<li><code>Systables</code> contains: tabname and tabid;</li>
<li><code>Syscolumns</code> contains: colname, tabid, colno.</li>
</ul>

<p>I have to write the following procedure:</p>

<ul>
<li>I have 2 new tables - Tabela_sys (contains ns_tabela and nome), and Campo_sys (ns_campo, ns_tabela, nome).</li>
<li>I want to store the data of systables that does not exist in tabela_sys and I want to store the data of syscolumns that does not exist in Campo_Sys.</li>
<li>For that, I made the following procedure and I tested one case in which I created a new column in an existing table. </li>
<li>It is expected that after executing my procedure, Tabela_sys will keep the number of rows equal and Campo_sys will recognize 1 new row. </li>
</ul>

<p>It is not happening and Campo_sys has many more rows, after performing the procedure.</p>

<p>Does anyone know what could be wrong?</p>

<pre><code>CREATE PROCEDURE test ()

DEFINE cont       INTEGER;
DEFINE cont2       INTEGER;
DEFINE TabIdNum       INTEGER;
DEFINE varTabName,aa       VARCHAR (50,0);
DEFINE Nstabela       INTEGER;
DEFINE StrColName       VARCHAR (50,0);
DEFINE ColIdNum       INTEGER;

    FOREACH cur1 WITH HOLD FOR

        SELECT  tabid, tabname
        INTO    TabIdNum,varTabName
        from    systables
        WHERE   tabname not matches "sys*"

        --let aa=varTabName;

        SELECT  count(*), ns_tabela
        INTO cont, Nstabela
        FROM tabela_sys
        WHERE nome = varTabName
        GROUP BY 2;

        --let Nstabela=0;

        IF cont = 0 or cont is null THEN    
            INSERT INTO tabela_sys (ns_tabela, nome) 
            VALUES (0, varTabName);

            SELECT dbinfo('sqlca.sqlerrd1')
            INTO   Nstabela
            FROM   systables
            WHERE  tabname='systables';
        END IF;

    FOREACH cur2 WITH HOLD FOR

        SELECT  tabid, colname 
        INTO    ColIdNum, StrColName
        FROM    syscolumns 
        WHERE   tabid = TabIdNum        
        --AND   colname NOT IN (SELECT nome FROM campo_sys WHERE ns_tabela = Nstabela)  

        SELECT  count(*)
        INTO cont2
        FROM campo_sys
        WHERE nome = StrColName
        and   ns_tabela = Nstabela;

        --let Nscampo=0;

        IF cont2 = 0 or cont2 is null  THEN 
            INSERT INTO campo_sys (ns_tabela, nome) --(ns_campo, ns_tabela, nome) 
            VALUES (ColIdNum, StrColName); --(0, ColIdNum, StrColName);

        END IF;

    END FOREACH

    END FOREACH

END PROCEDURE;
</code></pre>