Q:

SQL Server Rollback transaction using two storedprocedures

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,

A:

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  

