execute 'Dynamic querie1' is a transaction ?

Question

Post reply

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?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

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;