How to rollback or commit a transaction in SQL Server?

How to rollback or commit a transaction in SQL Server?

 

Begin Try
    Begin Transaction 
	
    // Here you write SQL codes
    Commit
End Try
Begin Catch 
    // implement error handling 
    Rollback
End Catch

 

CREATE PROCEDURE spProject
 @ProjectID INT,
 @MemberID INT,
 @Name VARCHAR(10)
 AS
 BEGIN
 BEGIN TRY
 BEGIN TRANSACTION;
 -- Insert record into Project table
 INSERT INTO tblProject(ProjectID, Name) VALUES(1, 'Project');
 -- Insert record into ProjectMember table
 INSERT INTO tblProjectMember(MemberID, ProjectID) VALUES(2, 1);
 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
 IF @@TRANCOUNT > 0
 ROLLBACK TRANSACTION;
 DECLARE @ErrorNumber INT = ERROR_NUMBER();
 DECLARE @ErrorLine INT = ERROR_LINE();
 DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
 PRINT 'Error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
 PRINT 'Line number: ' + CAST(@ErrorLine AS VARCHAR(10));
 RAISERROR(@ErrorMessage);
 END CATCH
 END;

2021-10-03