question

EDIT You could of course use TRY/CATCH to capture failures as well, if you just want to make sure you don't continue to execute after an error has occured. CREATE PROC mainProc2 AS BEGIN TRY exec p1 exec p2 exec p3 exec p4 exec p5 END TRY BEGIN CATCH --Do some error handling here PRINT 'error' END CATCH END EDIT You could look at the return value from each stored procedure and RETURN if the result 0. Something like this: CREATE PROC MainProc AS DECLARE @res int exec @res=p1 IF @res0 RETURN ELSE PRINT 'P1 executed successfully' exec @res=p2 IF @res0 RETURN ELSE PRINT 'P2 executed successfully' exec @res=p3 IF @res0 RETURN ELSE PRINT 'P3 executed successfully' exec @res=p4 IF @res0 RETURN ELSE PRINT 'P4 executed successfully' exec @res=p5 IF @res0 RETURN ELSE PRINT 'P5 executed successfully' I also would like to advice you to not name your stored procedures sp\_something. Stored procedures named sp\_something are treated in a special way by SQL Server, and the worst result you might end up with is that you can't execute the stored procedure. Why? Because if there exists a stored procedure with the same name in the master database, that stored procedure will be executed instead of yours. That's true even if you try to execute it with a three-part-name (database.schema.storedprocedurename). Here's an example: use YourLocalDB GO CREATE PROC dbo.sp_help AS PRINT 'executing local stored proc' GO exec dbo.sp_help GO exec YourLocalDB.dbo.sp_help GO Please try that code out and then decide if you still want to name your stored procedures sp\_something :)

In addition to Magnus answer, you could use Begin try Exec yourproc1 exec yourproc2 End try Begin catch -- catch any error End catch

In addition to our GURUs' above answers, I would also try to handle the errors in each procedure in order to make a **successful execution** (Any procedure can be called individually somewhere else as well). I have seen people sometimes forgot to handle scenarios where TRY..CATCH OR XACT_ABORT should be used. For e.g. below is a very typical example, where foreign key error is generated, but still result in a partial Insert which is more dangerous USE [tempdb] GO CREATE TABLE temp ( tempid INT PRIMARY KEY ) CREATE TABLE temp2 ( id INT IDENTITY(1, 1) PRIMARY KEY ,tempid INT CONSTRAINT FK_Tempid FOREIGN KEY REFERENCES [temp] ( [tempid] ) ) GO CREATE PROCEDURE sp1 AS BEGIN TRANSACTION INSERT [temp] SELECT 1 INSERT [temp2] SELECT 2 -- Foreign key error. IF @@TRANCOUNT > 0 COMMIT TRANSACTION GO EXEC sp1 GO SELECT * FROM temp --WILL RETURN ONE ROW, WHICH WAS NOT THE IDEAL CASE GO ---DROP ALL CREATED OBJECTS DROP PROCEDURE sp1 DROP TABLE [temp2] DROP TABLE [temp] Any input on this from our GURUs will be highly appreciated and would bring more knowledge to us. Thanks.