How to stop sequence of SQL stored procedure execution if one among them fails?

Question

Here is one scenario i came across, - I have a SQL job which has around four SQL stored procedure - These are getting executed sequentially one after another - Now Case is : If any of the stored procedure fails or raise an exception, entire Job should get halt.

How I could do that ?

Answer 1

Make yourself a little procedure with something like this:

BEGIN TRY

     DECLARE @Return INTEGER

     -- Run first procedure 
     EXEC @Return = firstProcedure
     IF (@Return <> 0)
     BEGIN
          -- Do some error handling 
     END

     -- Run second procedure 
     EXEC @Return = secondProcedure
     IF (@Return <> 0)
     BEGIN
          -- Do some error handling 
     END

     -- etc...

END TRY
BEGIN CATCH
    -- Do some error handling 
END CATCH

Answer 2

Although there are several different ways to do this, I would suggest that you use the facilities in SQL Server Agent. Make each of the calls a separate step in the job.

This will allow you to move from one step to the next when successful. You'll also be able to use SQL Server Agent's logging and error handling mechanisms to determine the error and handle it.