SQL Server Stored Procedure Try Catch Return code issue
Posted: Tue Apr 04, 2017 2:06 am
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
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