How can I call a procedure declared inside another procedure?

Question

I have a doubt regarding procedures in Db2. I created a stored procedure using "CREATE PROCEDURE", and inside that I have another which is declared using "DECLARE PROCEDURE". But, this procedure created by declare, cannot be called using "CALL".

When I try to run the procedure, Db2 returns:

[Code: -440, SQL State: 42884] DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=DMTLDBR.SP_DASH_CALENDARIO.PROCURA_DIA_UTIL_POST_DIA1;PROCEDURE, DRIVER=4.25.1301

How should I call the declared procedure? (PROCURA_DIA_UTIL_POST_DIA1)

Code:

create or replace PROCEDURE           "SP_DASH_CALENDARIO" (IN P_MES INTEGER)
BEGIN

------
DECLARE SQLSTATE                       CHAR(5) DEFAULT ' ';

DECLARE V_ANO_MES                      DECIMAL(6); 
DECLARE V_ID_DIA                       ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIA;

DECLARE V_VAL_REAL_CAL                 ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_TONVNEMA                 ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONVNEMA;
DECLARE V_VAL_FAT357_CAL               ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357; 
DECLARE V_VAL_PERDA_FAT_CAL            ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_PERDA;

DECLARE V_SUM_VAL_REAL_CAL             ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_REAL_CAL;
DECLARE V_SUM_VAL_TONVNE_CAL           ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONVNE_CAL;
DECLARE V_SUM_VAL_TONLIQUIDO_ZBCL_CAL  ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_ZBCL_CAL;
DECLARE V_SUM_VAL_TONLIQUIDO_CAL       ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_CAL;
DECLARE V_SUM_VAL_FAT357_CAL           ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_FAT357_CAL;   

DECLARE V_VAL_TONLIQUIDO_CAL           ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_CAL;
DECLARE V_VAL_TONVNE_CAL               ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONVNE_CAL;
DECLARE V_VAL_TONLIQUIDO_ZBCL_CAL      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONLIQUIDO_ZBCL_CAL; 

DECLARE V_VAL_PLANO_FAT_CAL            ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_PLANEJ_MAN.VAL_PLANO; 
DECLARE V_VAL_PLANO                    ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_PLANEJ_MAN.VAL_PLANO; 
DECLARE V_TOTAL_PLANO_DESOVA_SEMANAL   ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_PLANEJ_MAN.TOTAL_PLANO_DESOVA_SEMANAL;

DECLARE V_ID_DIASEMANA                 ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIASEMANA;
DECLARE V_FLG_HOLIDAY                  ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.FLG_HOLIDAY;

DECLARE V_IND_LEGENDA                  DECIMAL(1);
DECLARE V_VARIANTE                     ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_PRODVARIANTE.ID_VARIANTE;

DECLARE V_VAL_PERDA                    ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_DESOVA                   ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_UHT                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_SUCO                     ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_VAL_REQ                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_TONLIQUIDO;
DECLARE V_FAT_UHT                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357;  
DECLARE V_FAT_REQ                      ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357; 


DECLARE V_ID_DIA_INI                   ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIA;
DECLARE V_ID_DIA_FIM                   ANCHOR DATA TYPE TO DMTLDBR.TB_DIM_DIADIA.ID_DIA;
DECLARE FLAG_DIA_UTIL                  BOOLEAN;
DECLARE V_SUM_VAL_TONVNEMA_CAL         ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_DASH_CALENDARIO.VAL_TONVNEMA_CAL;
DECLARE V_SUM_VAL_FAT357               ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_FAT357;
DECLARE V_SUM_VAL_ABATIMENTOREPORTADO  ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_ABATIMENTOREPORTADO;
DECLARE V_SUM_VAL_ICMSZFMREPORTADO     ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_ICMSZFMREPORTADO;

DECLARE V_PROC                         ANCHOR DATA TYPE TO DMTLDBR.TB_TMP_LOG_EXECUTION_PROC.SYNCPOINT_PROC;
SET V_PROC = 'SP_DASH_CALENDARIO';

-- =========================================================================================================
--   P R O C   P R O C U R A _ D I A _ U T I L _ P O S T _ D I A 1
--
--  OBTEM O PRIMEIRO DIA ÚTIL DO MÊS (QUE NÃO SEJA DOMINGO NEM FERIADO)
--
-- =========================================================================================================
BEGIN
DECLARE PROCEDURE PROCURA_DIA_UTIL_POST_DIA1(IN P_ID_MES INTEGER)
BEGIN

        DECLARE V_ACHOU  DECIMAL(1) DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN END;

        SET V_ID_DIA = NULL;

        L1: LOOP -- (LP01)

        BEGIN  -- (BE02.)

                DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN END;

                SELECT MIN(ID_DIA) INTO V_ID_DIA
                FROM   DMTLDBR.TB_DIM_DIADIA
                WHERE  ID_MES     = P_ID_MES
                AND    ID_DIASEMANA  <> 1  -- DOMINGO
                AND    FLG_HOLIDAY = 0;

        END; -- (BE02.)

        IF SQLSTATE = '00000' THEN
                LEAVE L1;
         --SET V_ACHOU = 1;
        END IF;

        END LOOP L1; -- (LP01.)

END;
END;

CALL DMTLDBR.PROCURA_DIA_UTIL_POST_DIA1(201909);

END

score 1 · Answer 1 · answered Oct 05 '19 at 08:11

A Compound SQL (compiled) statement requires strict order of declarations / statements inside.
For example:

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE TEST_LOCAL(P_I INT)
BEGIN
    -- SQL-variable-declarations
    DECLARE L_I INT;

    -- DECLARE-CURSOR-statements
    DECLARE C1 CURSOR FOR VALUES 1;

    -- procedure-declaration
    DECLARE PROCEDURE TEST_LOCAL_NESTED(P_J INT) BEGIN END;

    -- handler-declarations
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;

    -- SQL-procedure-statements
    CALL TEST_LOCAL_NESTED(P_I);
END@

Briefly for each BEGIN END block:

1-st: all SQL-variable-declarations
2-nd: all DECLARE-CURSOR-statements
3-rd: all procedure-declaration
4-th: all handler-declarations

All SQL-procedure-statements come afterwards only. These statements may contain nested BEGIN END blocks, where the same strict declaration / statement rules apply.

mao · Accepted Answer · 2019-10-05T09:25:53.623

You have more than one mistake in the code shown in the question.

You are using a local procedure, that is the name for a procedure declared inside another procedure.

Local-Procedures work fine when used correctly.

Some suggestions to allow successful compilation:

the declare procedure must appear before any executable statements or new block in the calling procedure.

Move the statement SET V_PROC = 'SP_DASH_CALENDARIO' so that it appears before the CALL to the local procedure (or anywhere within the main procedure block AFTER all declarations (including after declaring the local procedure).

Additionally, remove the BEGIN (and matching END) on the line before DECLARE PROCEDURE. You want all the declares to appear before any executable statement or new begin-end block.
the local procedure name must be unqualified both on the DECLARE and on the CALL but you are using a qualifier on your CALL statement which will prevent Db2 from finding the local procedure.

The last part of your procedure will then look something like below:

...snip...

DECLARE V_SUM_VAL_ICMSZFMREPORTADO     ANCHOR DATA TYPE TO DMTLDBR.TB_FATO_VENDASDIARIO.VAL_ICMSZFMREPORTADO;
DECLARE V_PROC                         ANCHOR DATA TYPE TO DMTLDBR.TB_TMP_LOG_EXECUTION_PROC.SYNCPOINT_PROC;


    -- =========================================================================================================
    --   P R O C   P R O C U R A _ D I A _ U T I L _ P O S T _ D I A 1
    --
    --  OBTEM O PRIMEIRO DIA ÚTIL DO MÊS (QUE NÃO SEJA DOMINGO NEM FERIADO)
    --
    -- =========================================================================================================
    DECLARE PROCEDURE PROCURA_DIA_UTIL_POST_DIA1(IN P_ID_MES INTEGER)
    BEGIN

            DECLARE V_ACHOU  DECIMAL(1) DEFAULT 0;
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN END;

            SET V_ID_DIA = NULL;

            L1: LOOP -- (LP01)

            BEGIN  -- (BE02.)

                    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN END;

                    SELECT MIN(ID_DIA) INTO V_ID_DIA
                    FROM   DMTLDBR.TB_DIM_DIADIA
                    WHERE  ID_MES     = P_ID_MES
                    AND    ID_DIASEMANA  <> 1  -- DOMINGO
                    AND    FLG_HOLIDAY = 0;

            END; -- (BE02.)

            IF SQLSTATE = '00000' THEN
                    LEAVE L1;
             --SET V_ACHOU = 1;
            END IF;

            END LOOP L1; -- (LP01.)

    END;

    SET V_PROC = 'SP_DASH_CALENDARIO';

    CALL PROCURA_DIA_UTIL_POST_DIA1(201909);

    END@

How can I call a procedure declared inside another procedure?

2 Answers2