I am calling a Teradata stored procedure through the Datastage STP stage.
For example:
REPLACE PROCEDURE x.A(
des VARCHAR(50)
,OUT ErrorMessage VARCHAR(500)
)
BEGIN
--- Deplay Error message if Table not found
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
set ErrorMessage = 'Table Not Found';
-- Insert record into batch table
INSERT INTO x.B
(
des
,time_ts
)
VALUES
(
description
,current_timestamp
);
END;
In this stored procedure , I am generating the error message 'Table Not Found'. But when call this procedure within the STP stage in Datastage it is considered as just another ordinary output column. But I have to call this job inside a sequence job which will pass the trigger to the next stage only if there is no error(which is actually determined by the "ErrorMessage"), otherwise the job needs to be aborted.
What should I do inorder for datastage to consider the manually generated ErrorMessage inside the stored procedure as an error/warning.
If you feel there is a better way to do it , please let me know... I am stuck!!
![Sad :(](./images/smilies/icon_sad.gif)