Multple insert, update and delete in sql server 2005

 private String InsertMultipleAudits()
    {
        StringBuilder AuditswrtAuditType = new StringBuilder();
        String AuditTypeID = String.Empty;
        AuditswrtAuditType.Append("<Root>");
        foreach (ListItem boxItem in chkBoxListSubAuditType.Items)
        {
            if (boxItem.Selected)
            {
                AuditTypeID = (String)boxItem.Value;
                AuditswrtAuditType.Append("<AuditTypes AuditID ='" + AuditTypeID + "'></AuditTypes>");
            }
        }
        AuditswrtAuditType.Append("</Root>");
        AuditswrtAuditType.ToString();
        return AuditswrtAuditType.ToString();
    }
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ============================================= 
-- Author:  <Manoj Thakur> 

-- ============================================= 
ALTER PROCEDURE [dbo].[InsertUpdateAuditPlan] 
 @ShipID bigint, 
 @FromPortID bigint, 
 @ToPortID bigint, 
 @IsAtSea bit, 
 @PlannedStartDate datetime, 
 @PlannedEndDate datetime, 
 @MultipleAuditsXML xml, 
 @MultipleChecklistXML xml, 
 @AuditorsAssignedXML xml, 
 @ShipAuditID bigint, 
 @ShipAuditName nvarchar(200)
 
AS 
BEGIN 
 SET NOCOUNT ON; 
 Begin Try
 if Exists(Select ShipAuditName From ADT_ShipAudit Where ShipAuditName = @ShipAuditName and EntityID <> @ShipAuditID)
        Begin
            Select 1 as Result
            return
End

Begin Tran   
 

 
  If(@ShipAuditID = 0) 
  Begin 
          Insert into ADT_ShipAudit( 
          ShipID,FromPortID,ToPortID,IsAtSea,PlannedStartDate,PlannedEndDate,ShipAuditName
          ) 
          Values 
          ( 
          @ShipID,@FromPortID,@ToPortID,@IsAtSea,@PlannedStartDate,@PlannedEndDate,@ShipAuditName 
          )  
           
          Declare @LIShipAuditID bigint =  @@IDENTITY 
           
       --- Insert Multiple Audits 
             
            Declare @idoc int     
            EXEC sp_xml_preparedocument @idoc OUTPUT, @MultipleAuditsXML 
             
             DECLARE @MultipleAudits TABLE     
               ( 
              AuditID bigint            
               )    
                         
               INSERT INTO @MultipleAudits             
               SELECT AuditID             
               FROM OPENXML (@idoc, '/Root/AuditTypes')             
              WITH (AuditID  bigint)   
                           
                           
             EXEC sp_xml_removedocument @idoc       
 
                
             INSERT INTO ADT_ShipAudit_Audits        
             (       
              ShipAuditID,     
              AuditID   
             )       
             SELECT @LIShipAuditID,ma.AuditID FROM @MultipleAudits ma   
            
            
        -- insert multiple checklist  
            
            
         Declare @chkidoc int    
         EXEC sp_xml_preparedocument @chkidoc OUTPUT, @MultipleChecklistXML 
 
          DECLARE @MultipleChecklist TABLE     
            ( 
           ChecklistID bigint            
            )    
                      
            INSERT INTO @MultipleChecklist             
            SELECT ChecklistID             
            FROM OPENXML (@chkidoc, '/Root/Checklist')             
           WITH (ChecklistID  bigint)   
                        
                        
          EXEC sp_xml_removedocument @chkidoc  
              
 
             
          INSERT INTO ADT_ShipAudit_Chklist        
          (       
           ShipAuditID,    
           ChecklistID  
          )       
          SELECT @LIShipAuditID,mc.ChecklistID FROM @MultipleChecklist mc   
           
           
          --- Insert Auditors assigned 
           
           
           Declare @AuditorsAssignedidoc int    
         EXEC sp_xml_preparedocument @AuditorsAssignedidoc OUTPUT, @AuditorsAssignedXML 
 
          DECLARE @AuditorsAssigned TABLE     
            ( 
           AuditorassignedID bigint,
           IsLeadAuditor bit            
            )    
                      
            INSERT INTO @AuditorsAssigned             
            SELECT AuditorassignedID,IsLeadAuditor            
            FROM OPENXML (@AuditorsAssignedidoc, '/Root/Auditors')             
           WITH (AuditorassignedID  bigint,IsLeadAuditor bit)   
                        
                        
          EXEC sp_xml_removedocument @AuditorsAssignedidoc  
              
 
             
          INSERT INTO ADT_ShipAudit_AssignedAuditor        
          (       
           ShipAuditID,    
           AuditorID,
           IsLeadAuditor 
          )       
          SELECT @LIShipAuditID,aa.AuditorassignedID,aa.IsLeadAuditor FROM @AuditorsAssigned aa  
            Select 2 as Result   
   end 

  Else IF(@ShipAuditID <> 0) 
   begin 
    
    Update ADT_ShipAudit Set  
    ShipID = @ShipID, 
    FromPortID = @FromPortID, 
    ToPortID = @ToPortID, 
    IsAtSea = @IsAtSea, 
    PlannedStartDate = @PlannedStartDate, 
    PlannedEndDate = @PlannedEndDate  where EntityID = @ShipAuditID 
           
           
   --- Update Multiple Audits 
               
                           
              Declare @SelectMultipleAudits TABLE -- Already Selected Multiple Audits Table
              (
              ShipAuditID bigint,
              AuditID bigint
              )
              Insert INTO @SelectMultipleAudits
              Select ShipAuditID,AuditID from ADT_ShipAudit_Audits where ShipAuditID = @ShipAuditID
       
         
             
            Declare @upidoc int     
            EXEC sp_xml_preparedocument @upidoc OUTPUT, @MultipleAuditsXML 
             
             DECLARE @UPMultipleAudits TABLE     -- SELECTED  Multiple Audits Table
               ( 
              AuditID bigint            
               )    
                         
               INSERT INTO @UPMultipleAudits             
               SELECT AuditID             
               FROM OPENXML (@upidoc, '/Root/AuditTypes')             
              WITH (AuditID  bigint)   
                           
                           
             EXEC sp_xml_removedocument @upidoc  
            
                         
             INSERT INTO ADT_ShipAudit_Audits        
             (       
              ShipAuditID,     
              AuditID   
             )       
             SELECT @ShipAuditID,upma.AuditID FROM @UPMultipleAudits upma
             WHERE AuditID NOT IN(SELECT AuditID FROM @SelectMultipleAudits)
            
             
         DELETE FROM ADT_ShipAudit_Audits WHERE ShipAuditID = @ShipAuditID and AuditID NOT IN(SELECT upma.AuditID FROM @UPMultipleAudits upma)
     
     -- update multiple checklist  
         
          Declare @SelectMultipleChecklist  TABLE -- Already Selected Multiple checklist table
              (
              ShipAuditID bigint,
              ChecklistID bigint
              )
              Insert INTO @SelectMultipleChecklist
              Select ShipAuditID,ChecklistID from ADT_ShipAudit_Chklist where ShipAuditID = @ShipAuditID
        
       
         Declare @upchkidoc int    
         EXEC sp_xml_preparedocument @upchkidoc OUTPUT, @MultipleChecklistXML 
 
          DECLARE @UPMultipleChecklist TABLE     -- Selected Multiple checklist table
            ( 
           ChecklistID bigint            
            )    
                      
            INSERT INTO @UPMultipleChecklist             
            SELECT ChecklistID             
            FROM OPENXML (@upchkidoc, '/Root/Checklist')             
           WITH (ChecklistID  bigint)   
                        
                        
          EXEC sp_xml_removedocument @upchkidoc  
              
       
             
          INSERT INTO ADT_ShipAudit_Chklist        
          (       
           ShipAuditID,    
           ChecklistID  
          )       
          SELECT @ShipAuditID,upmc.ChecklistID FROM @UPMultipleChecklist upmc 
            WHERE upmc.ChecklistID NOT IN(SELECT ChecklistID FROM @SelectMultipleChecklist)
            
            
           DELETE FROM ADT_ShipAudit_Chklist WHERE ShipAuditID = @ShipAuditID and ChecklistID NOT IN(SELECT upmc.ChecklistID FROM @UPMultipleChecklist upmc) 

 -- update multiple AssignedAuditor 
  
            Declare @SelectAuditorsAssigned  TABLE -- Already Selected AssignedAuditor table
              (
              ShipAuditID bigint,
              AuditorassignedID bigint,
              IsLeadAuditor bit
              )
              Insert INTO @SelectAuditorsAssigned
              Select ShipAuditID,AuditorID,IsLeadAuditor from ADT_ShipAudit_AssignedAuditor where ShipAuditID = @ShipAuditID
             
             

        Declare @UPAuditorsAssignedidoc int    
         EXEC sp_xml_preparedocument @UPAuditorsAssignedidoc OUTPUT, @AuditorsAssignedXML 
 
          DECLARE @UPAuditorsAssigned TABLE     -- Selected AssignedAuditor table
            ( 
           AuditorassignedID bigint,
           IsLeadAuditor bit                      
            )    
                      
            INSERT INTO @UPAuditorsAssigned             
            SELECT AuditorassignedID,IsLeadAuditor            
            FROM OPENXML (@UPAuditorsAssignedidoc, '/Root/Auditors')             
           WITH (AuditorassignedID  bigint,IsLeadAuditor bit )   
                        
                        
          EXEC sp_xml_removedocument @UPAuditorsAssignedidoc  
           
             
          INSERT INTO ADT_ShipAudit_AssignedAuditor        
          (       
           ShipAuditID,    
           AuditorID,
           IsLeadAuditor 
          )       
          SELECT @ShipAuditID,upaa.AuditorassignedID,upaa.IsLeadAuditor FROM @UPAuditorsAssigned upaa  
            WHERE upaa.AuditorassignedID NOT IN(SELECT AuditorassignedID FROM @SelectAuditorsAssigned)
           
         
         DELETE FROM ADT_ShipAudit_AssignedAuditor WHERE ShipAuditID = @ShipAuditID and AuditorID NOT IN(SELECT upaa.AuditorassignedID FROM @UPAuditorsAssigned upaa)
                
         Select 3 as Result
   end    
  Commit Tran 

 End Try 
 Begin Catch 
  Rollback Tran
Select 4 as Result
 End Catch 
END