SQL Server : stored procedure transaction

Question

Hello I got some stored procedures to create products and other stuff on my site. Now I have to run some of them in a transaction. Is that possible or do I have to make a stored procedure only for the transaction?

Can I say something like

BEGIN TRAN
"1. stored procedure"
"2. stored procedure"
COMMIT

Answer 1

To add to the other answers above, you may want to add some error handling:

BEGIN TRAN  BEGIN TRY     EXEC P1     EXEC P2     COMMIT TRAN  END TRY BEGIN CATCH    ROLLBACK TRAN  END CATCH 

---

Update with C# code (I personally find it a lot easier to keep trans code out of the sprocs and in the data layer - makes composing stored procedures easier at a later stage):

using (var conn = new SqlConnection(...))      trans = conn.BeginTransaction();      try    {        ...call P1 using transaction        ...call P2 using transaction         trans.Commit();    }    catch    {        trans.RollBack();        throw;    } } 

Answer 2

Yes, a stored procedure can be run inside a transaction. Please find below a sample query.

create table temp1
(
    id int,
    name varchar(20)
)

create table temp2
(
    id int,
    name varchar(20)
)
go

create proc p1 as
insert temp1 values (1, 'test1')


create proc p2 as 
insert temp2 values (1, 'test2')
go  

begin tran tx
exec p1
exec p2
commit

Answer 3

From SQL Server (not sure about other RDBMS), You can call multiple stored procedures inside a transaction.

BEGIN TRAN
EXEC StoredProc1
EXEC StoredProc2
COMMIT TRAN

You may want to add a return code to the stored proc to check if you should run stored proc 2 if stored proc 1 failed

EDIT: To check a return code you can do something like the following. This will run the first stored proc. If it returns 0 then it runs the 2nd. If the 2nd returns 0 then it commits the transaction. If either returns non-0 then it will rollback the transaction

DECLARE @ReturnValue INT
BEGIN TRAN
  EXEC @ReturnValue = StoredProc1
  IF @ReturnValue = 0
  BEGIN
    EXEC @ReturnValue = StoredProc2
    IF @ReturnValue = 0
    BEGIN
      COMMIT
    END
    ELSE
    BEGIN
      ROLLBACK
    END
  END
  ELSE
  BEGIN
    ROLLBACK
  END