Stored Procedures - how to handle dynamic result set

General
Enthusiast

Stored Procedures - how to handle dynamic result set

Hi all,

I've got the following problem: how to handle dynamic result sets from calling procedure in a caller in TD12?

replace procedure test_result_set()

dynamic result sets 1

begin

    declare c_result_set cursor with return only for

        select

            day_of_calendar,

            day_of_week

        from

            sys_calendar.calendar

        where

            day_of_calendar < 10

    ;        

    open c_result_set;

end;

Second procedure should invoke first one and return result set(same as first proc).

This method doesn't work, so how it should be done ?

Allocate keyword is unavailable in teradata 12.

replace procedure test2_result_set()

dynamic result sets 1

begin

    declare statement1_str varchar(500);

    declare result_set cursor with return only for stmt1;

    set statement1_str = 'call test_result_set()';

    prepare stmt1 from statement1_str;

    open result_set;

end;

Thanks in advance

5 REPLIES
Teradata Employee

Re: Stored Procedures - how to handle dynamic result set

Did you try "WITH RETURN TO CLIENT" instead of "WITH RETURN ONLY"

The documentation states:


If you specify TO CLIENT, the result set is returned to the client application even if called from a nested stored procedure.

Junior Contributor

Re: Stored Procedures - how to handle dynamic result set


You simply can't do that in TD12.



Dieter


Enthusiast

Re: Stored Procedures - how to handle dynamic result set


Hi NetFx,



The second procedure is just an example it just pass result set from first procedure to client but in fact I want do something with result set returned from first procedure and then send result to client.



WITH RETURN TO CLIENT causes that result set goes to client application such as teradata assistant.



 



 



 



 



 



 


Enthusiast

Re: Stored Procedures - how to handle dynamic result set

Thanks Dieter, This is what I was afraid of

Enthusiast

Re: Stored Procedures - how to handle dynamic result set

Hi Dieter,

In which case we write DEALLOCATE PREPARE STMT; ?

Thanks in Advance,

Sheetal