Procedures Calling Procedures with Dynamic Result Sets (from TD notes)

Database
Enthusiast

Procedures Calling Procedures with Dynamic Result Sets (from TD notes)

Hello all,

I am teaching myself the syntax of stored procedures from your notes. P35 from "SQL Stored Procedures and Embedded SQL" shows the following snippit:

REPLACE PROCEDURE alloc007()

DYNAMIC RESULT SETS 1

BEGIN

    DECLARE EmpNo0 SMALLINT;

    DECLARE ProjId0 CHAR(8);

    DECLARE WkEnd0 DATE;

    DECLARE Hours0 DECIMAL(4,1);

    DECLARE ee0 CHAR(8);

    DECLARE ff0 VARCHAR(25);

    DECLARE gg0 DATE;

    DECLARE hh0 DATE;

    DECLARE ii0 DATE;

    CALL drs_temp5();

    ALLOCATE my_fetch CURSOR FOR PROCEDURE drs_temp5;

    FETCH FIRST FROM my_fetch INTO empno0,projid0,wkend0,hours0;

    INSERT INTO charges_temp2(empno0,projid0,wkend0,hours0);

    WHILE (SQLCODE = 0)

    DO

        FETCH NEXT FROM my_fetch INTO empno0,projid0,wkend0,hours0;

        IF (SQLCODE = 0)

        THEN

        INSERT INTO charges_temp2(empno0,projid0,wkend0,hours0);

        END IF;

    END WHILE;

    -- close the current result set cursor

    CLOSE my_fetch;

    -- see if there are result sets

    WHILE (SQLSTATE = '0100D')

    DO

        -- allocate the next one.

        ALLOCATE sp2 CURSOR FOR PROCEDURE drs_temp5;

        WHILE (SQLCODE = 0)

        DO

        FETCH NEXT FROM sp2 INTO ee0,ff0,gg0,hh0,ii0;

        IF (SQLCODE = 0)

        THEN

        INSERT INTO project_temp1(ee0,ff0,gg0,hh0,ii0);

        END IF;

        END WHILE;

        CLOSE sp2;

    END WHILE;

END;

So I have hooked up some code to execute some of this:

CREATE MULTISET TABLE t1 (

    empno0 SMALLINT

    ,projid0 CHAR(8)

    ,wkend0 DATE

    ,hours0 DECIMAL(4,1)

);

INSERT INTO t1 VALUES (1,'helllo',CURRENT_DATE,'11.01');

INSERT INTO t1 VALUES (2,'helllo',CURRENT_DATE,'11.01');

CREATE MULTISET TABLE charges_temp2 (

    empno0 SMALLINT

    ,projid0 CHAR(8)

    ,wkend0 DATE

    ,hours0 DECIMAL(4,1)

);

REPLACE PROCEDURE drs_temp5()  

--DYNAMIC RESULT SETS 1

BEGIN

    DECLARE cursor1 NO SCROLL CURSOR WITH RETURN ONLY FOR

    SELECT

        empno0

        ,projid0

        ,wkend0

        ,hours0

    FROM t1;

END;

CALL drs_temp5();--works

REPLACE PROCEDURE alloc007()  

DYNAMIC RESULT SETS 1

BEGIN

    DECLARE EmpNo0 SMALLINT;

    DECLARE ProjId0 CHAR(8);

    DECLARE WkEnd0 DATE;

    DECLARE Hours0 DECIMAL(4,1);

    DECLARE ee0 CHAR(8);

    DECLARE ff0 VARCHAR(25);

    DECLARE gg0 DATE;

    DECLARE hh0 DATE;

    DECLARE ii0 DATE;

    CALL drs_temp5();

    ALLOCATE my_fetch CURSOR FOR PROCEDURE drs_temp5;        

    FETCH FIRST FROM my_fetch INTO empno0,projid0,wkend0,hours0;

    INSERT INTO charges_temp2(:empno0,:projid0,:wkend0,:hours0);

    WHILE (SQLCODE = 0)

    DO

        FETCH NEXT FROM my_fetch INTO empno0,projid0,wkend0,hours0;

        IF (SQLCODE = 0)

            THEN INSERT INTO charges_temp2(:empno0,:projid0,:wkend0,:hours0);

        END IF;

    END WHILE;

    CLOSE my_fetch;

/*

    -- see if there are result sets

    WHILE (SQLSTATE = '0100D')

    DO

    -- allocate the next one.

    ALLOCATE sp2 CURSOR FOR PROCEDURE drs_temp5;

    WHILE (SQLCODE = 0)

    DO

        FETCH NEXT FROM sp2 INTO ee0,ff0,gg0,hh0,ii0;

        IF (SQLCODE = 0)

        THEN

        INSERT INTO project_temp1(ee0,ff0,gg0,hh0,ii0);

        END IF;

    END WHILE;    

    CLOSE sp2;

    END WHILE;

*/

END;

CALL alloc007();

But this fails to be called any ideas?

3 REPLIES
Teradata Employee

Re: Procedures Calling Procedures with Dynamic Result Sets (from TD notes)

There has to be some error description, code .... ?

Enthusiast

Re: Procedures Calling Procedures with Dynamic Result Sets (from TD notes)

Error:3523 temp6 owner referenced by user does not have execute procedure access to MDM_WRK.temp5.

Enthusiast

Re: Procedures Calling Procedures with Dynamic Result Sets (from TD notes)

Okay I discovered the problem. I needed EXECUTE PROCEDURE WITH GRANT privs to daisy chain procedures together. Due to the need to grant access by the procedure to the other procedure as the caller.

Addendum, I also had to extend the first macro to include an OPEN Cursor1; statement otherwise I get a null dataset from the first macro. Happy days!