I have a stored procedure in Teradata that used to work but some changes recently have rendered the output of the procedure non-existent. Partial code is pasted below in-case anyone can find the error or how to turn off the error handling. But what I'm looking for here is a way to track the error in the procedure. If I try to run the individual elements of the procedure separately they fail and if I run the full procedure it runs but doesn't tell me where it failed and moved on.
Is there a way in Teradata to see where things are going wrong?
REPLACE PROCEDURE HP_FL_OWN.RX_INITIATIVES_UM_SP()
BEGIN
DECLARE VAR_DB_NAME, VAR_TABLE_NAME VARCHAR(60);
DECLARE ACT_INFO VARCHAR(1000);
DECLARE STEP_NAME VARCHAR(100);
DECLARE ACT_NAME VARCHAR(100);
DECLARE ACT_ID INT;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Error Handling step
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* DECLARE EXIT HANDLER FOR SqlException */
BEGIN
SELECT SqlState INTO ACT_INFO;
-- SET ACT_INFO=SqlState;
-- if error occurs in 01 or 02
IF STEP_NAME IN('01-Verify EDW Completion', '02-Verify Current Execution')THEN
-- insert error log record for step 01 or step 02
CALL HP_FL_OWN.OPR_ACTIVITY_LOG_INSERT_SP(ACT_NAME, Current_Timestamp(0), 'E',STEP_NAME,'ERROR_SQLSTATE:'||ACT_INFO,ACT_ID);--create record in log table for the day
ELSE -- if error occurs in any other step
-- update error log record with that step
CALL HP_FL_OWN.OPR_ACTIVITY_LOG_UPDATE_SP(ACT_ID,'E',STEP_NAME,'ERROR_SQLSTATE:'||ACT_INFO);---update record in log table when process completes
END IF;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Begin SP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I've tried looking at the history but it does not involved any detailed info on what line(s) are failing.