Need help on working with Teradata Stored Procedure

[jaya_joseph]

Hi all,

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!!

[chulett]

But when call this procedure within the STP stage in Datastage it is considered as just another ordinary output column.

That's because it is 'just another ordinary output column'. Can you not just check the value after the SP stage and then 'manually' abort the job?

There is also an Error Codes tab on the stage where you can define your own handling but I'm not sure if that would check an output column. Have you checked it out? Documented in the Connectivity Guide for Stored Procedures pdf, btw.

[jaya_joseph]

Thanks Craig for your reply... and sorry for the late response..

I have dropped the idea of implementing this through a stored procedure...
Because as you said the column is just considered as another output column rather than an error message... neither could I find a proper way to utilise the User defined error code option in the SP stage itself... it could be the same reason that the error is passed as an output...

Alternate ways of implementing this would be using a log table to pass the error and then to abort the job based on the value passed.Or, using a user defined routine or so... But all those seemed to be an unnecessary over load, so I implemented the same logic using Datastage job itslef which solved the problem and served my purpose.