January 12, 2015 at 11:45 am
In SSIS, you can have a parent package call a child package. If that child package runs successfully, then the parent package can call the next child package. If the first child package fails, then the parent package won't call anything else.
I'm totally confused as to how to do this with stored procedures. It's the same concept, I'm just lost regarding the application. My child stored procedures have Try/Catch code within them, but I'm lost beyond that.
January 12, 2015 at 12:03 pm
Put the parent proc in a try/catch also.
A procedure will return 0 if it is successful, and non-zero if it fails.
In the parent proc, you can do this:
DECLARE @ReturnValue int
EXEC @ReturnValue = YourFirstChildProc
If @ReturnValue <> 0 Begin
RAISEERROR(blah, blah, blah) -- Or THROW if you are on the right version.
End
EXEC @ReturnValue = YourSecondChildProc
If @ReturnValue <> 0 Begin
RAISEERROR(blah, blah, blah)
End
Does this get you closer to what you want to do?
If you need some more elaborate tests to determine what to execute next, you can add some output parameters to the child procs, test the value, and act accordingly.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 12, 2015 at 12:28 pm
Thanks, I think I understand what you did. Here's my modified version below. Does it make sense?
-- Modified version
CREATE PROCEDURE USP_Wrapper
AS
BEGIN
DECLARE @ReturnValue int
EXEC @ReturnValue = USP_Child01
IF @ReturnValue <> 0
BEGIN
RAISERROR ('USP_Child01 failed, processing stopped.')
END
EXEC @ReturnValue = USP_Child02
IF @ReturnValue <> 0
BEGIN
RAISERROR (USP_Child02 failed, processing stopped.')
END
EXEC @ReturnValue = USP_Child03
IF @ReturnValue <> 0
BEGIN
RAISERROR ('USP_Child03 failed, processing stopped.')
END
END
GO
January 12, 2015 at 12:39 pm
One more thing. What if I want the error message from the child sproc's TRY/CATCH to be visible to the parent sproc?
January 12, 2015 at 12:41 pm
Try it out. Create a series of procs that generate errors.
See what happens.
The errors bubble up.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply