DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
CREATE   PROC  usp_ClearBarcode
AS 
SET NOCOUNT ON

DECLARE @charMO varchar(16),@intClearID int,@intClearCount int

SET @charMO=''
SET @intClearID=0
SET @intClearCount=0

DECLARE csClearBarcode INSENSITIVE CURSOR 
FOR
SELECT ClearID,MO_Code FROM dbo.Clear_Barcode WHERE Finished=0 ORDER BY ClearID

OPEN csClearBarcode

FETCH NEXT FROM csClearBarcode
INTO 
@intClearID,@charMO

WHILE @@FETCH_STATUS = 0
BEGIN

SET @intClearCount=@intClearCount+1

BEGIN TRAN  ClearBarcode

---------------------------
---Backup Barcode
--------------------------

INSERT BarcodeBackup..BarcodeBak  SELECT *,GETDATE() FROM  Barcode (NOLOCK)  WHERE MO_Code=@charMO and Isnull(Code,'')<>'VOID'

 IF @@ERROR <> 0
    BEGIN
      RAISERROR('Error occurred, cannot BACKUP  Barcode  %d', 16, 1, @charMO)
      ROLLBACK TRAN ClearBarcode
      GOTO RESUME_NEXT
  END  

-----delete barcode

DELETE Barcode WHERE MO_Code=@charMO

 IF @@ERROR <> 0
    BEGIN
      RAISERROR('Error occurred, cannot delete Barcode  %d', 16, 1, @charMO)
      ROLLBACK TRAN ClearBarcode
      GOTO RESUME_NEXT
  END  

------delete mo_bundle

DELETE MO_Bundle WHERE  MO_Code=@charMO

 IF @@ERROR <> 0
    BEGIN
      RAISERROR('Error occurred, cannot delete MO_Bundle  %d', 16, 1, @charMO)
      ROLLBACK TRAN ClearBarcode
      GOTO RESUME_NEXT
  END  

------delete mo_bundle_range

DELETE MO_Bundle_Range WHERE  MO_Code=@charMO

 IF @@ERROR <> 0
    BEGIN
      RAISERROR('Error occurred, cannot delete MO_Bundle_Range  %d', 16, 1, @charMO)
      ROLLBACK TRAN ClearBarcode
      GOTO RESUME_NEXT
  END  

-----update Clear_Barcode

UPDATE Clear_Barcode SET Finished=1,ClearedDateTime =getdate()  WHERE ClearID=@intClearID

 IF @@ERROR <> 0
    BEGIN
      RAISERROR('Error occurred, cannot update ClearBarcode  %d', 16, 1, @intClearID)
      ROLLBACK TRAN ClearBarcode
      GOTO RESUME_NEXT
  END  

COMMIT TRAN  ClearBarcode

IF @intClearCount % 10=0
  BEGIN
    DUMP TRAN PTP WITH NO_LOG
    DUMP TRAN BarcodeBackup WITH NO_LOG
  END

RESUME_NEXT:
  FETCH NEXT FROM csClearBarcode
  INTO 
  @intClearID,@charMO

END

DUMP TRAN PTP WITH NO_LOG
DUMP TRAN BarcodeBackup WITH NO_LOG

EXIT_PROC:
CLOSE csClearBarcode
DEALLOCATE csClearBarcode
RETURN 0

ERROR_PROC:
CLOSE csClearBarcode
DEALLOCATE csClearBarcode
RETURN -1

GO