SQL Server Rollback transaction using two storedprocedures

Question

I am working on a C++ application that interact with SQL Server data.

I use a stored procedure to insert a data in one table.

After inserting the data, after few lines of code, I do a check ( can do this only after inserting the data) and if that check fails then I want to rollback my previous transaction. Please note I don't want to delete data at this point of time.

Is there any transaction service (with two stored procedures), so that I can call second stored procedure and rollback the previous transaction.

In short, what I need is pairing of two stored procedures - first SP insert and second SP commit/rollback the transaction based on my condition. Is it possible?

Thanks in advance,

score 1 · Accepted Answer · answered Aug 13 '14 at 01:20

I would say the best thing would be to create a master stored procedure that begins and ends the transaction.

In pseudocode, something like this:

CREATE PROCEDURE MASTERSP
AS
BEGIN
  BEGIN TRANSACTION
  EXEC FirstSP
  EXEC @Result = SecondSP

  IF @RESULT= -1 
      ROLLBACK 
  ELSE
      COMMIT 
END

SQL Server Rollback transaction using two storedprocedures

1 Answers1