Selecting multiple rows using Stored Proc

[GopiKrishnan_S]

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.0

REPLACE PROCEDURE STOR_PROC.SQL3 (OUT PERIOD_DATE DATE)

BEGIN

DECLARE SQL3CURSOR CURSOR FOR
SELECT
COL1
FROM
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]

Hello,

You need to put "DYNAMIC RESULT SETS " clause as well.

Example (from manual):

CREATE PROCEDURE Sample_p (INOUT c INTEGER)
DYNAMIC RESULT SETS 2
BEGIN
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
SELECT * FROM m1;
DECLARE cur2 CURSOR WITH RETURN ONLY FOR
SELECT * FROM m2 WHERE m2.a > c;
SET c = c +1;
OPEN cur1;
OPEN cur2;
END;

HTH.

Regards,

Adeel

[Fred]

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]

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

[rdharma]

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 2
BEGIN
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
SELECT * FROM m1;
DECLARE cur2 CURSOR WITH RETURN ONLY FOR
SELECT * 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]

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