Posted by Dan Guzman on 12/15/06 13:02

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

<joel.sjoo@gmail.com> wrote in message
news:1166184223.625115.296200@79g2000cws.googlegroups.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
>

• Next in forum: How to create the ER Diagram for SQL Server Database objects
• Prev in forum: Re: Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition
• Thread view: Re: Execute proceduers from another Proceduer with error handling

[Reply to this message]