USE [AdventureWorks2]
GO



DROP PROCEDURE HumanResources.UpdateAllEmployeeHireDateInefficiently
GO

CREATE PROCEDURE HumanResources.UpdateAllEmployeeHireDateInefficiently 
AS
BEGIN TRY
 SET XACT_ABORT ON  
 DECLARE curemployee CURSOR FOR SELECT TOP 10
  EmployeeID FROM HumanResources.Employee FOR UPDATE
 OPEN curemployee 
 FETCH FROM curemployee 
 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
   UPDATE HumanResources.Employee 
   SET HireDate = GETDATE() 
   WHERE CURRENT OF curemployee  
   FETCH FROM curemployee -- to avoid the output row, store it into variable
 END 
END TRY 
BEGIN CATCH 
 ROLLBACK TRANSACTION 
 PRINT 'An error occured, transaction rolled back' 
END CATCH 
COMMIT;
GO

EXEC HumanResources.UpdateAllEmployeeHireDateInefficiently