CREATE PROCEDURE dbo.SaveHotelsDepartment @AdepartmentString VARCHAR (37), @AdepartmentSubString VARCHAR (37), @AHotelNumberList VARCHAR (100), @ReturnedDepartmentString Varchar (37) OUTPUT  AS
SET NOCOUNT ON
-- Last Changed AV 2007 1012 fix for not deleting departments
--  Created AV 20050622

--DECLARE @FullDepartmentString Varchar (37)

  DECLARE @Idx INT
  DECLARE @CurrentDepartment VARCHAR (1)
  DECLARE @HotelNumber int
  DECLARE @AdepartmentStringCopy VARCHAR (37)
  SET @ADepartmentStringCopy = @ADepartmentString
  SET @ReturnedDepartmentString = ''
  SET @AHotelNumberList = ','  +@AHotelNumberList + ','

  IF @AdepartmentString <> '' 
  BEGIN
    --First check if any deps should be deleted from the full department string
    SET @Idx = 1
    WHILE @Idx < = LEN(@ADepartmentStringCopy) 
    BEGIN
      SET @CurrentDepartment  = SUBSTRING( @ADepartmentStringCopy, @Idx, 1)
      SELECT @HotelNumber = HotelNumber FROM Departments WITH (NOLOCK) WHERE DepartmentNumber = @CurrentDepartment

      IF (CHARINDEX (',' + CONVERT(VARCHAR (3),@HotelNumber) + ',', @AHotelNumberList) > 0) AND (CHARINDEX (@CurrentDepartment, @AdepartmentSubString) = 0)
        SET @AdepartmentString = STUFF(@AdepartmentString, @idx,1,'') --OKAY THIS Department must be deleted from the full string

      SET @Idx = @Idx + 1
    END

     -- Then check if we should add some new departments to the full department string
    SET @Idx = 1
    WHILE @Idx < = LEN(@ADepartmentSubString) 
    BEGIN
      SET @CurrentDepartment  = SUBSTRING( @ADepartmentSubString, @Idx, 1)

      IF CHARINDEX (@CurrentDepartment, @AdepartmentString) = 0 --okay the current department is not in the full string so add it.
        SET @AdepartmentString = @AdepartmentString + @CurrentDepartment
      SET @Idx = @Idx + 1
    END
    
  END


  -- Now sort The string:

    CREATE TABLE #SortTable (Department Varchar (1))

    SET @Idx = 1
    WHILE @Idx < = LEN(@AdepartmentString) 
    BEGIN
      SET @CurrentDepartment  = SUBSTRING( @AdepartmentString, @Idx, 1)
      INSERT INTO #SortTable VALUES (@CurrentDepartment)
       SET @Idx = @Idx + 1
    END
        
   SET @CurrentDepartment = ''
   DECLARE #SortCur CURSOR LOCAL FAST_FORWARD FOR
   SELECT Department FROM #SortTable WITH (NOLOCK) ORDER BY Department

   OPEN #SortCur
   FETCH NEXT FROM #SortCur
   INTO @CurrentDepartment
   WHILE(@@Fetch_status=0)
   BEGIN
     SET @ReturnedDepartmentString = @ReturnedDepartmentString + @CurrentDepartment    
     Fetch Next From #SortCur into @CurrentDepartment
   END

   CLOSE #SortCur
   DEALLOCATE #SortCur

   DROP TABLE #SortTable
--  Set @ReturnedDepartmentString = @ADepartmentString
GO
