multiple SPs has a single transaction.

[dfwcharles]

Is it possible to run multiple Stored Procedures like a single transaction?

Example
in the Query Analyzer window I run this:

myproc '@p1','@p2,'@p3','@p4'
go
myproc '@p1','@p2,'@p3','@p4'
go
myproc '@p1','@p2,'@p3','@p4'
go
myproc '@p1','@p2,'@p3','@p4'
go

if one of these SP returns an error, I dont want the following SPs to run.

Any ideas?
Thanks
Charles

 

[tlbroadbent]

You have to remove the 'GO' statements. That makes each SP execution a batch which is treated as a transaction. You'll also need to enclose the entire script in a Transaction and add error handling.

Begin Transaction

Exec myproc '@p1','@p2,'@p3','@p4'
If @error > 0 Goto ErrorHandler

Exec myproc '@p1','@p2,'@p3','@p4'
If @error > 0 Goto ErrorHandler

Exec myproc '@p1','@p2,'@p3','@p4'
If @error > 0 Goto ErrorHandler

Exec myproc '@p1','@p2,'@p3','@p4'
If @error > 0 Goto ErrorHandler

Commit

Print 'The process succeeded.'
Return

ErrorHandler:

Rollback

Print 'An error and rollback occurred.'
Return Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[dfwcharles]

Thanks Tlbroadbent, That's what I'll do.

Thanks
Charles