Database

BEGIN TRAN T1

Insert into table2
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'Insert into table2 failed'
    return
  END

exec sp_2
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'exec of sp2 failed'
    return
  END

exec sp_3
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'exec of sp3 failed'
    return
  END

Insert into table3
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'Insert into table3 failed'
    return
  END

Delete from table1 (data collected and now inserted into the new tables)

if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'Delete from table1 failed'
    return
  END

COMMIT TRANSACTION 

<br />
SqlConnection cn = new SqlConnection("connectionstring");<br />
SqlTransaction trans = cn.BeginTransaction();<br />
<br />
SqlCommand cmd = new SqlCommand("storedprocedurename");<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
<br />
try<br />
{<br />
	cmd.ExecuteNonQuery();<br />
	trans.Commit();<br />
}<br />
catch(SqlException e)<br />
{<br />
	trans.Rollback();<br />
}<br />