Simple teradata stored procedure

Question

The web seems a bit short on working examples for something that should be quite common. A plain Jane example of "Get me some records". This is my first ever Stored proc and all I want is to see some records. Why is that so flipping hard? ;-) I figure that if I can get one example that works, I can tune it from there into something I can really use. This is taken from another example I found on the web. Doesn't compile because CURSOR declaration is a syntax error of some sort.

CREATE PROCEDURE "SCHEMA"."GETRESULTSET (
    IN "p1" VARCHAR(30))
DYNAMIC RESULT SETS 1
BEGIN       
DECLARE CURSOR cur1 WITH RETURN ONLY TO CLIENT FOR 
 SELECT partitioninfo FROM SCHEMA.SessionInfo where username = p1;    
  OPEN cur1;    
END;

Anyway, sure could use a clue. I saw an example where the CURSOR was declared separately from the SQL but then there wasn't an example that showed how to get the variable into the SQL when it was declared as a VARCHAR. The example I am working off of was pretty old but it's the best I could find.

Rob Paller · Accepted Answer · 2012-07-28T14:28:21.950

A Teradata MACRO would be a better solution for what you have described.

REPLACE MACRO [MyDB].GetResultSet(p1 VARCHAR(30)) AS (
SELECT "Partition"
  FROM DBC.SessionInfo
 WHERE UserName = :p1;
);

EXEC MyDB.GetResultSet

While a stored procedure could be used to accomplish the task cursor based logic used in other popular database systems (Oracle, SQL Server, etc.) can lead to bad programming habits in Teradata. Generally, processing large cursors in Teradata degrades performance. Many things that you have have used cursor for in other DBMS' are better served to be done with SET based logic where possible.

That being said cursors can be used successfully in Teradata to perform certain tasks that require conditional logic or dynamic SQL processing. I hope this helps and if you have a more concrete example you need help with I'd be happy to offer some suggestions.

Edit: This DDL for your procedure works against Teradata 13.10:

CREATE PROCEDURE "SCHEMA"."GETRESULTSET" (
    IN "p1" VARCHAR(30))
DYNAMIC RESULT SETS 1
BEGIN       
DECLARE cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR 
 SELECT "Partition" FROM "SCHEMA".SessionInfo where username = p1;    
  OPEN cur1;    
END;

The cursor name has to proceed the CURSOR key word. PartitionInfo is not a valid column on DBC.SessionInfo. Not sure if "Schema" gets replaced by MyBatis or not, so you may have to tweak the SELECT statement.

Oops, my bad. I should have been more clear about my objectives. The idea was to come up with a stored procedure that returned a record set so that we could later test stored procedures that returned record sets against MyBatis for a Proof of Concept. Hmmmmm.... It might be interesting to add the test case though. Can Mybatis call a Macro? Good question. — user447607, Jul 28 '12 at 13:04
:) - Small world. See my edit for a tested version of your Stored Procedure that ran against Teradata 13.10. In my tested copy I replaced "SCHEMA".SessionInfo with "DBC".SessionInfo. — Rob Paller, Jul 28 '12 at 14:29
Yes, I had figured it out also but had not had time to update the example. Kudos to you. :-) — user447607, Jul 31 '12 at 16:11

Simple teradata stored procedure

1 Answers1