CODEWITHSUNDEEP
stored-proceduresteradata
Aser
Aser

Reputation: 27

Creating store procedure at teradata

there is error at sql while creating stored procedure at teradata 16 , please what is the wrong with this query?

REPLACE PROCEDURE tutorial_db.banks(out test Varchar(20)) BEGIN select name from tutorial_db.banks END;

Upvotes: 0

Views: 784

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Teradata's SPs are based on Standard SQL, to return a result set you must define a cursor

REPLACE PROCEDURE tutorial_db.banks(OUT test VARCHAR(20)) 
DYNAMIC RESULT SETS 1 -- add 1 for each result set to be returned
BEGIN  
   -- dummy cursor declaration
   DECLARE cur2 CURSOR WITH RETURN ONLY FOR      
   SELECT NAME FROM tutorial_db.banks;

   -- don't close within the SP
   OPEN cur2;
END;

Cursors are usually bad but this WITH RETURN ONLY cursor is not processed row by row within the SP, it's just an answer set.

Upvotes: 1

Related Questions