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
SQL Server Stored Procedure Try Catch Return code issue
Moderators: chulett, rschirm, roy
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.
A search here for "try catch" only finds your post.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
create procedure sp_parent as
begin try
exec sp_child
end try
begin catch
return(1)
end catch