Execute proceduers from another Proceduer with error handling

[joel.sjoo]

I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO
Regard Joel

[Dan Guzman]

Check the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:

DECLARE @ReturnCode int
EXEC @ReturnCode = dbo.usp_TEST1
IF @ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@ReturnCode)
END
GO

DECLARE @ReturnCode int
EXEC @ReturnCode = dbo.usp_TEST2
IF @ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@ReturnCode)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

<jo*******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...

>I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO
Regard Joel

[Erland Sommarskog]

Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

Check the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:

DECLARE @ReturnCode int
EXEC @ReturnCode = dbo.usp_TEST1
IF @ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@ReturnCode)
END
GO

DECLARE @ReturnCode int
EXEC @ReturnCode = dbo.usp_TEST2
IF @ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@ReturnCode)
END

Even better is this check:

IF @Returcode <0 OR @@error <0

The procedure may not set a return code in case of errors, and there
are errors where the proceudure does not return a value at all. (More
precisely compilation error, in which case the procedure is terminated
and execution continues with the next statement.)

If Joel is on SQL 2005 he should of course use TRY CATCH, but since he
using OSQL, I assmue that he is on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

(jo*******@gmail.com) writes:

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

I don't relly know what this is supposed to be, but note that the first
GO marks the end of USP_RUNJOB, so the call to SP_TEST2 is not part of
that procedure.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx