Call stored procedure problem

Database
Tourist

Call stored procedure problem

 Hi everyone. I am new at teradata. If you mind i want to ask you a question.

 here is my script.

----------------------
CREATE TABLE AAA (XX BIGINT);

INS INTO AAA VALUES (123456);

 

REPLACE PROCEDURE pppp()
DYNAMIC RESULT SETS 8
SQL SECURITY CREATOR
BEGIN

DECLARE SQL1 VARCHAR(1000);
DECLARE SQL2 VARCHAR(10000);
DECLARE SQL3 VARCHAR(10000);
DECLARE SQL4 VARCHAR(10000);
DECLARE SQL5 VARCHAR(10000);
DECLARE SQL6 VARCHAR(10000);
DECLARE SQL7 VARCHAR(10000);
DECLARE SQL8 VARCHAR(10000);
DECLARE crsr1 CURSOR WITH RETURN TO CLIENT FOR stmt1;
DECLARE crsr2 CURSOR WITH RETURN TO CLIENT FOR stmt2;
DECLARE crsr3 CURSOR WITH RETURN TO CLIENT FOR stmt3;
DECLARE crsr4 CURSOR WITH RETURN TO CLIENT FOR stmt4;
DECLARE crsr5 CURSOR WITH RETURN TO CLIENT FOR stmt5;
DECLARE crsr6 CURSOR WITH RETURN TO CLIENT FOR stmt6;
DECLARE crsr7 CURSOR WITH RETURN TO CLIENT FOR stmt7;
DECLARE crsr8 CURSOR WITH RETURN TO CLIENT FOR stmt8;

SET SQL1 = 'select 1;';
PREPARE stmt1 FROM SQL1;
OPEN crsr1;


SET SQL2 = 'SELECT 2 ;';
PREPARE stmt2 FROM SQL2;
OPEN crsr2;


SET SQL3 = 'SELECT 3 ;';
PREPARE stmt3 FROM SQL3;
OPEN crsr3;


SET SQL4 = 'SELECT 4 ;';
PREPARE stmt4 FROM SQL4;
OPEN crsr4;
SET SQL5 = 'SELECT *FROM AAA WHERE XX=123456';
PREPARE stmt5 FROM SQL5;
OPEN crsr5;

SET SQL6 = 'SELECT *from AAA WHERE XX=111111;';
PREPARE stmt6 FROM SQL6;
OPEN crsr6;

SET SQL7 = 'select *FROM AAA WHERE XX=123456 ;' ;
PREPARE stmt7 FROM SQL7;
OPEN crsr7;

SET SQL8 = 'select 8 ;' ;
PREPARE stmt8 FROM SQL8;
OPEN crsr8;
END;



call pppp();

--ANSWERSET 1,2,3,4,5 getting results
--ANSWERSET 6 is not getting results
--ANSWERSET 7,8 is not getting results. how can i get results ?
-----------------

how can i get results in 7. and 8. queries ?

 

thanks for your helps.

1 REPLY
Teradata Employee

Re: Call stored procedure problem

What client are you using to run this?  I pasted your SQL into Teradata Studio and when I call the procedure I get all 8 resultsets back.