execute 'Dynamic querie1' is a transaction ?

• sm_iransoftware

  Ten Centuries

  Points: 1228

  More actions

  February 7, 2015 at 2:24 am

  #295194

  Hi

  I have this command :

  Exec 'update .... insert ..... delete ..... insert ...'

  I Execute these command in one execution.

  exec ('...')

  Are these commands act as a transaction ?

  If one of them create error , another commands run or rull backed?

  (I test : If all right , All run without problem. If one of them raise error , nothing run)

  is this right for all conditions?

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  February 7, 2015 at 3:00 am

  #1776207

  Quick thought, each statement will execute regardless of the success of other statements in the dynamic code, hence errors and transactions must be explicitly handled, consider this example

  😎

  USE tempdb;

  GO

  IF OBJECT_ID(N'dbo.TBL_TRAN_TEST') IS NOT NULL DROP TABLE dbo.TBL_TRAN_TEST;

  CREATE TABLE dbo.TBL_TRAN_TEST

  (

  TRT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

  ,TRT_VAL INT NOT NULL

  ,TRT_TRANCOUNT INT NOT NULL DEFAULT(@@TRANCOUNT)

  ,TRT_NESTLEVEL INT NOT NULL DEFAULT(@@NESTLEVEL)

  )

  ;

  DECLARE @SQL_STR NVARCHAR(MAX) = N'

  BEGIN TRY

  BEGIN TRAN

  INSERT INTO dbo.TBL_TRAN_TEST(TRT_VAL) VALUES(100);

  SELECT * FROM dbo.TBL_TRAN_TEST;

  -- THIS WILL FAIL

  INSERT INTO dbo.TBL_TRAN_TEST(TRT_VAL) VALUES(NULL);

  SELECT * FROM dbo.TBL_TRAN_TEST;

  UPDATE dbo.TBL_TRAN_TEST SET TRT_VAL = TRT_VAL + 1;

  SELECT * FROM dbo.TBL_TRAN_TEST;

  INSERT INTO dbo.TBL_TRAN_TEST(TRT_VAL) VALUES(200);

  COMMIT TRAN

  END TRY

  BEGIN CATCH

  ROLLBACK

  END CATCH

  '

  EXEC (@SQL_STR);

  SELECT * FROM dbo.TBL_TRAN_TEST;