We have a situation where the customer wants to output the results of the Teradata 14.0 Stored Procedure through a front end Java Application. Most of the Stored Procedures they have do not contain an OUT parameter and they want to capture a whole bunch of col1, col2 output to the client application through a SELECT operation. Couple of questions:
1) My understanding is that to output the result set, we should use DYNAMIC RESULT SET option. If DYNAMIC RESULT SET is used, should that be used with a combination of WITH RETURN TO CLIENT and by declaring CURSORs?
2) The Stored Procedures are not using CURSORS and don't see a point in rewriting them to use CURSORS (prefer SET based approach)
3) If we can't use a combination of DYNAMIC RESULT SET and WITH RETURN TO CLIENT, is dumping the result to a VOLATILE table the only option?
Below is the sample stored procedure that is used:
REPLACE proc b2b_se_sign_s ( IN se10 BIGINT, IN max_age_days INT DEFAULT 21 )
-- se_sign can have multiple rows per se10. We only want the most recent one.
-- seq_nbr is the primary key on se_sign, and is a bigint comprised of the julian date
-- when loaded into the table concatenated with the record number from that load.
-- max(seq_nbr) will therefore return the most recently loaded row for an se10.
-- :max_age_days default of 21 is a completely arbitrary limit,
-- based on the assumption that no legitimate SE data in se_sign meant to move
-- quickly into se_char should remain exclusively in se_sign for more than 21 days.
-- This assumption is based on some data analysis and se_char's weekly cycle time.
DECLARE target_seq_nbr BIGINT; DECLARE max_age_dt DATE;
-- Get the start of the desired range
SET max_age_dt = dateadd(DAY, -1 * :max_age_days, convert(DATE, getdate())); -- Need to replace this with TD functions
-- Get the PK for the most recent row with insert_dt in the desired range
SET target_seq_nbr =
( SELECT MAX(ss1.seq_nbr)
FROM se_sign ss1
WHERE ss1.se10 = :se10
AND ss1.insert_dt > max_age_dt
GROUP BY ss1.se10
-- Select that row
-- Note that insert_dt is selected as open_dt because se_sign contains only SEIMS signings,
-- and se_sign.insert_dt generally corresponds to se_char_seims.open_dt.
-- There are some se10s for which insert_dt != open_dt,
-- but I am going to assume these are unusual circumstances like reinstates or SE10 changes.
SELECT ss1.se10 ,
ss1.insert_dt AS open_dt,
'Y' AS recent_sign_ind
FROM se_sign SS
WHERE ss1.seq_nbr = :target_seq_nbr ;
A cursor used for "dynamic result set" is just named cursor, but it's not fetched sequentially. It's like any result set from a SELECT, just using a more complicated syntax :-)
Thus a Volatile Table is only better when you have to access the result multiple times or use it in another SQL statement.
This seems to be a port from MS SQL Server, where SPs are used a lot to utilise parameters. As long as they use only simple logic (without loops, etc.) you might replace them with macros.
, -1 * :max_age_days,
-> CURRENT_DATE - :max_age_days;
Btw, your example is easy to rewrite as a single select in a view.
can you compile your procedure:
CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
DECLARE SqlStr VARCHAR(1000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
PREPARE S1 FROM SqlStr;
OPEN C1 USING EmpName;
FETCH C1 INTO Salary;
I am getting: SPL5009:E(L5), Dynamic cursor 'C1' is not allowed with WITHOUT RETURN, WITH RETURN TO CALLER/CLIENT.
Of course, what's your exact release? You should check if dynamic cursors are supported in yoiur release.
I just compiled it on a TD13.