CREATE PROCEDURE DBO.GetHotelsAsString  @ADepartmentString VARCHAR (37), @Return VARCHAR (500) output AS
SET NOCOUNT ON

  DECLARE @Idx INT
  DECLARE @CurrentDepartment VARCHAR (1)
  DECLARE @CurrentHotelName VARCHAR (50)
  DECLARE @CurrentHotelNumber INT

  SET @Return = ''

  SET @Idx = 1
  WHILE @Idx < = LEN(@ADepartmentString) 
  BEGIN
    SET @CurrentDepartment  = SUBSTRING( @ADepartmentString, @Idx, 1)
    SELECT @CurrentHotelNumber = HotelNumber FROM Departments WITH (NOLOCK)  WHERE DepartmentNumber = @CurrentDepartment   
    
    SELECT @CurrentHotelName = LastName FROM Customer WITH (NOLOCK) WHERE HotelNumber = @CurrentHotelNumber

    IF CHARINDEX (@CurrentHotelName, @Return) = 0
    BEGIN
      IF @Return <>  ''
        SET @Return =  @Return  + ','

      SET @Return = @Return + @CurrentHotelName
    END

    SET @Idx = @Idx + 1
  END

