SQL Server Stored Procedure Try Catch Return code issue

[ecclesr]

I have a stored procedure (2 input parameters) which includes the Try Catch structure to handle any SQL Server Stored errors

For testing (SQL Server errors handling) if I include an invalid sql statement section
..
BEGIN TRY
....
....
SELECT 1/0
....
.....
END TRY

BEGIN CATCH
return(3)
END CATCH

When the stored procedure is run from SSMS it returns the expected return code 3 is returned.

But when I run the Store Procedure from the Stored Procedure Stage (Transform) the return code is 0

Note all other coded return codes in the stored procedure are reported correctly by DataStage - it is only the value from Try Catch that is not

Has anyone had this issue with not getting the expected return codes when Try Catch error handing is used - is there a work around

Thanking you all in advance

[chulett]

We'll see if anyone has had any experience with this issue but any time I see something like "everything about it works as expected except for this one thing" you are probably firmly in support's hands.

A search here for "try catch" only finds your post.

[ecclesr]

With some further investigation - solution I found is to have two levels of stored procedures with the original store procedure becoming the child stored procedure and DataStage calls the parent stored procedure

create procedure sp_parent as
begin try
exec sp_child
end try
begin catch
return(1)
end catch