我如何执行一个SP，并根据它的结果执行另一个查询，如果失败，应该恢复SP所做的工作?

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

我认为这是一个基本的SQL问题…新…:-)

I want to execute a SP inside which

我想在其中执行一个SP

{

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?

我想执行一个SP(使用EXEC)，根据它的结果，我想执行另一个查询(插入)，如果由于任何原因失败，应该恢复SP所做的工作?

}

How can I do this?

我该怎么做呢?

Transactions? If yes, how?

事务?如果是,如何?

PLEASE NOTE THAT THE INNER SP SENDS A MAIL.

请注意，内部SP发送邮件。

1 个解决方案

#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