All Forums Database
GopiKrishnan S 18 posts Joined 02/08
27 Nov 2008
Selecting multiple rows using Stored Proc

Hi All,When i execute the below stored proc, am getting just one row as output, though the sql returns many rows when executed separately. Kindly help me to resolve this.I am calling the SP from SQL Asst. in Teradata 12.0REPLACE PROCEDURE STOR_PROC.SQL3 (OUT PERIOD_DATE DATE) BEGIN DECLARE SQL3CURSOR CURSOR FOR SELECT COL1FROM TABLE1 ; OPEN SQL3CURSOR; LABEL1: LOOP FETCH SQL3CURSOR INTO PERIOD_DATE; IF (SQLSTATE = '02000') THEN LEAVE LABEL1; END IF; END LOOP LABEL1; CLOSE SQL3CURSOR; END;Thanks in Advance

Adeel Chaudhry 773 posts Joined 04/08
27 Nov 2008

Hello,You need to put "DYNAMIC RESULT SETS " clause as well.Example (from manual):CREATE PROCEDURE Sample_p (INOUT c INTEGER)DYNAMIC RESULT SETS 2BEGINDECLARE cur1 CURSOR WITH RETURN ONLY FORSELECT * FROM m1;DECLARE cur2 CURSOR WITH RETURN ONLY FORSELECT * FROM m2 WHERE m2.a > c;SET c = c +1;OPEN cur1;OPEN cur2;END;HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Fred 1096 posts Joined 08/04
27 Nov 2008

To return a result set, you declare the cursor WITH RETURN, OPEN the cursor, and leave it open when the procedure exits. You don't CLOSE it, and often won't FETCH from it either. And as mentioned, you'll need the DYNAMIC RESULT SETS keyword.

GopiKrishnan S 18 posts Joined 02/08
28 Nov 2008

Hi Adeel and Fred, Thanks a ton, it worked for me now.

rdharma 1 post Joined 01/09
14 Jan 2009

Hi,can somebody help me to see if this "DYNAMIC RESULT SETS " is a TD version 12. feature?I am executing a cut and paste from TD books and am getting the following result:-CREATE PROCEDURE Sample_p (INOUT c INTEGER)DYNAMIC RESULT SETS 2BEGINDECLARE cur1 CURSOR WITH RETURN ONLY FORSELECT * FROM m1;DECLARE cur2 CURSOR WITH RETURN ONLY FORSELECT * FROM m2 WHERE m2.a > c;SET c = c +1;OPEN cur1;OPEN cur2;END;THe results I get are:-SPL1048:E(L2), Unexpected text 'RESULT' in place of SPL statement.SPL1008:E(L2), Unexpected text 'RESULT' in place of literal or system variable.SPL1027:E(L2), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between the word 'RESULT' and the 'SETS' keyword.'.Can somebody help? Regards. Ram.

cnhan 11 posts Joined 12/07
14 Jan 2009

Yes, DYNAMIC RESULT SETS is a new feature in TD12.

You must sign in to leave a comment.