Add transactions in Stored Procedures

Create   Procedure P_proc
As
Begin   Tran
Set Xact_abort On  
Insert   Into Trantest (ID, test) Values ( 1 , ' Test ' )
Insert   Into Trantest (ID, test) Values ( 2 , ' Test ' )
Commit   Tran
Go
Set Xact_abort On Roll back immediately when an error is encountered
You can also write
Create   Procedure Pr_p
As  
Begin   Tran  
Insert   Into Trantest (ID, test) Values ( 1 , ' Test ' )
If ( @ Error <> 0 )
  Rollback   Tran  
Else  
  Begin  
Insert   Into Trantest (ID, test) Values ( 2 , ' Test ' )
If ( @ Error <> 0 )
Rollback   Tran  
Else  
Commit   Tran  
  End  
Go