Returning a Results from Stored Procedure

Database

Returning a Results from Stored Procedure

I am new to Teradata and I am trying to convert and Oracle stored procedure.  I have been successful at getting the procedure to run but I need to know how to get the correct results from the results set.  In our Oracle procedure we run a set of queries to insert data into a table and then sent the results from that table in a ref cursor. How can we do the same in Teradata?  I have gotten as far as running the procedure and and inserts the data into the tables.  Does anyone know if teradata has the equivalent of a ref cursor as in Oracle?

Tags (1)
4 REPLIES
Junior Contributor

Re: Returning a Results from Stored Procedure

Do you actually need to access the result several times?

Then you should materialize it in a Global Temporary Table.

When you only want to return it to the client, it's 

replace procedure xxx (...)
dynamic result sets 1
begin
...
declare rslt cursor with return only to client for
select .....;
open rslt; -- don't close it!!

Dieter

 

 

Re: Returning a Results from Stored Procedure

Thanks so much.  That gave me the results I was looking for.

Enthusiast

Re: Returning a Results from Stored Procedure

newbie here joined to groom my knowledge and also hope to enjoy my stay here

Enthusiast

Re: Returning a Results from Stored Procedure

replace PROCEDURE dbschema.sp_test2  
(
 IN "SRC_DB_NM" VARCHAR(30)
, In "SRC_TBL_NM" VARCHAR(30)
)    
DYNAMIC RESULT SETS 1
BEGIN      
DECLARE cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR
 'select * from ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';   
  OPEN cur1;   
END;

Hi Dieter,

How can I make the above dynamic sql work please?

Thanks,

HC