SQL Server begin transaction and commit inside of an cursor

• ypeng5

  Mr or Mrs. 500

  Points: 592

  More actions

  June 20, 2016 at 3:24 pm

  #327191

  I have stored procedure as following, and I am using a batch file to call this sp. anyone can help me to take a look at this process and see if there is somewhere wrong. There is a sql build in stored procedure which already have a commit in it. but I want to make sure that anytime if I stop the whole store procedure, anything inside of this commit part can be rollback

  BEGIN TRY

  BEGIN TRAN

  sp_sqlstoredprocedure

  INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);

  COMMIT;

  END TRY

  BEGIN CATCH

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

  ROLLBACK;

  END CATCH

  GO

• durga.palepu

  Hall of Fame

  Points: 3530

  More actions

  June 21, 2016 at 12:30 am

  #1885953

  first thing, you need not define another transaction inside the stored proc.

  Except that it seems your code is perfect.

• ypeng5

  Mr or Mrs. 500

  Points: 592

  More actions

  June 21, 2016 at 7:22 am

  #1886021

  If I didn't define the transaction, the rollback will not work, right?