Execute SQL Server stored procedures sequentially.

Question

I have list of stored procedure. spr_name1 spr_name2 spr_name3 spr_name4 spr_name5 Now i want to run this stored procedures sequentially . How can we implement this ? code side or database side ? One more condition is there. If any exception occurred at spr_name3 then result of spr_name1 and spr_name2 should be rollback . It's obvious that spr_name4 and spr_name5 will not run after exception occured. Need help in this scenario.

Akshay Phadke · Answer

You should try to create store procedure and call that multiple store procedures inside CREATE Proc TESTING AS Declare @TEST int EXEC @TEST=SP1 IF TEST1<>0 RETURN ELSE PRINT Success EXEC @TEST=SP2 IF TEST<>0 RETURN ELSE PRint Success EXEC @TEST=SP3 IF TEST<>0 RETURN ELSE Print Success and so on

Rahul Chavan · Answer

Use Transaction While calling the stored procedure

Akshay Phadke · Answer

Juts tRy this and you neeed to put in try catch block BEGIN TRY BEGIN TRANSACTION exec( @sp1) exec(@sp2) exec(@sp3) COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 (Validate it ) ROLLBACK (rollback to sp where you want to ) END CATCH

Avikshith Aradhya · Answer

If written with in Transaction block then we can rollback all the executed stored procedures with in that Block.

Avikshith Aradhya · Answer

you can try below sql code block to fix the problem: BEGIN TRY BEGIN TRANSACTION EXEC [dbo].[SProc1] EXEC [dbo].[SProc2] EXEC [dbo].[SProc3] --etc.... COMMIT END TRY BEGIN CATCH IFXACT_STATE()!=0 BEGIN ROLLBACK TRANSACTION END --select query to select error details can be written here to know more about -- error END CATCH by using a transaction around everything it will attempt to lock any concurrent users from working on the same data. Using the TRY-CATCH will attempt to catch any errors that might happen in one procedure and prevent the next ones from then running.

Akshay Phadke · Answer

use goto statement http://stackoverflow.com/questions/569473/sql-goto-statement-using-variable-label

Vivek Sheth · Answer

Thank you for providing answer but If any exception occurred at spr_name3 then result of spr_name1 and spr_name2 should be rollback . How can we achieve this ?