Execute SQL Server stored procedures sequentially.

Vivek Sheth

10y3.2k

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.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akshay Phadke10y ago
use goto statement
http://stackoverflow.com/questions/569473/sql-goto-statement-using-variable-label
0
Rahul Chavan10y ago
Use Transaction While calling the stored procedure
+1
Akshay Phadke10y ago
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
+1
Avikshith Aradhya10y ago
If written with in Transaction block then we can rollback all the executed stored procedures with in that Block.
+1
Vivek Sheth10y ago
Thank you for providing answer butIf any exception occurred at spr_name3 then result of spr_name1 and spr_name2 should be rollback.
How can we achieve this ?
0
Akshay Phadke10y ago
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
+1
Avikshith Aradhya10y ago
you can try below sql code block to fix the problem:
BEGINTRY
BEGINTRANSACTION
EXEC[dbo].[SProc1]
EXEC[dbo].[SProc2]
EXEC[dbo].[SProc3]
--etc....
COMMIT
ENDTRY
BEGINCATCH
IFXACT_STATE()!=0
BEGIN
ROLLBACKTRANSACTION
END
--select query to select error details can be written here, to know more about
-- error
ENDCATCH
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.
+1