CREATE FUNCTION dbo.GetDepartmentsFromHotels(@ADepartmentString VARCHAR (37), @AHotelNumberList VARCHAR (100))
RETURNS VARCHAR (37)
AS
BEGIN

  DECLARE @HotelNumber INT
  DECLARE @CurrentDepartment VARCHAR (1)
  DECLARE @Idx INT

  DECLARE @Return VARCHAR (37)

  SET @Return = ''
  SET @AHotelNumberList = ','  +@AHotelNumberList + ','

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

    IF CHARINDEX (',' + CONVERT(VARCHAR (3),@HotelNumber) + ',', @AHotelNumberList) > 0
      SET @Return = @Return + @CurrentDepartment

    SET @Idx = @Idx + 1
  END

  RETURN @Return
 
END

