How can I execute a SP and depending on it's result execute another query which if fails should revert back what the SP has done?

Question

I think it's a basic SQL question...sorry..new to it... :-)

I want to execute a SP inside which

{

I want to execute a SP (using EXEC) and depending on it's result I want to execute another query (INSERT) which if fails for any reason should revert back what the SP has done?

}

How can I do this?

Transactions? If yes, how?

PLEASE NOTE THAT THE INNER SP SENDS A MAIL.

Answer 1

Something like this:

BEGIN TRAN

DECLARE @rc int

EXEC @rc = EXEC inner_proc

IF (@rc = 1)
   INSERT something

IF @@ERROR <> 0
    ROLLBACK
ELSE
    COMMIT