how to maintain transactions for multiple insert statements in sql server 2005

• satishthota

  SSC Eights!

  Points: 959

  More actions

  March 16, 2009 at 9:15 am

  #135654

  how to maintain transactions for multiple insert statements in sql server 2005

  ex

  Create Procedure MyProc

  @Id int,

  @Name varchar(50),

  @Fname Varchar(50),

  @Deptno int

  as

  Begin

  Insert into t1 values(@Id,@Name)

  Insert into t2 values(@Id,@FName)

  Insert into t3 values(@Id,@Deptno)

  End

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 16, 2009 at 9:42 am

  #960366

  Hello

  CREATE PROCEDURE MyProc

  @Id int,

  @Name varchar(50),

  @Fname Varchar(50),

  @Deptno int

  as

  BEGIN TRY

  BEGIN TRANSACTION

  INSERT INTO t1 values(@Id,@Name)

  INSERT INTO t2 values(@Id,@FName)

  INSERT INTO t3 values(@Id,@Deptno)

  COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

  IF (XACT_STATE() = -1)

  ROLLBACK TRANSACTION

  IF (XACT_STATE() = 1)

  COMMIT TRANSACTION

  END CATCH

  Greets

  Flo