Ali Nafees Ali Nafees - 5 months ago 17
SQL Question

I am using the Transaction in sql to force all the query results in success.But It is not working

I am using the Transaction in sql to force all the query results in success.But It is not working.
What to do to set it so that all queries run successfully and if error occur in any query then all queries should not be executed.
this is how I am using the transactions in sql server....

BEGIN TRANSACTION
GO
INSERT [form].[control](Id,Name,Title,ElementType,IsRequired,Length,MinValue,MaxValue,Mask,DefaultValue,OptionType,DbType,AddOn,AddOnBefore,ShowHide,ShowHideCtrlType,DisabledCtrl,DisabledCtrlType,IsActive,IsHidden,ParentId,CreatedBy,CreatedOn,UpdatedBy,Updatedon,Class,Multiple)
VALUES('43',null,'Current Medical & Mental Health Diagnoses','2',null,null,null,null,null,null,null,'1',null,null,null,null,null,null,'1',null,null,'1',convert(datetime,'Jan 1 2016 12:00AM'),null,null,'15','0')
update form.Control
set class= NULL
where id = 43

COMMIT TRANSACTION ;

Answer Source

Basically, what you do is something like this:

BEGIN TRANSACTION;
BEGIN TRY

-- Multiple sql statements goes here

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

Note that the commit transaction must be the last statement in the try block. This way, the transaction is only committed if no statement inside the try block raised an error, and is rolled back only if there was an error.