exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as follows:

Exec Sp1

Inserts, updates

Exec Sp2

Inserts, upadtes

Exec Sp3

If error Exit Exec Sp1

Can I end Sp1 if I catch an error in Sp3

No, all modification made by Sp1 and Sp2 won't be rolled back. Because SQL treat the 3 Sps a batch: if there is any exception in the batch during execution, the statements executed before the one that encountered the run-time error are not affected, unless statements are put in one transction. For more information, please take a look at this link:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_04_9i9f.asp?frame=true