CREATE FUNCTION dbo.GetPacketNumberHotelsStr (@APacketNumber INT)  
/*  
  Created by: Roy Nielsen
  Purpose: Return a string of hotelnames for a packetnumber
*/
RETURNS VARCHAR(8000) AS  
BEGIN 
  DECLARE  @Result VARCHAR (8000)
  SET @Result = ''

  DECLARE @PacketNumber INT
  DECLARE @HotelNumber INT
  DECLARE @HotelName VARCHAR(255)

  DECLARE db_cur CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT DISTINCT PacketNumber,D.HotelNumber,C.LastName FROM dbo.Departments D WITH (NOLOCK)
    INNER JOIN dbo.PacketItem_NewDepartmentXTab PDX WITH (NOLOCK) ON PK_Department=FK_Department AND PDX.Type=1
    INNER JOIN dbo.PacketItem_New WITH (NOLOCK) ON PacketItem_New.PacketItemNumber=PDX.PacketItemNumber
    INNER JOIN dbo.Customer C WITH (NOLOCK) on ((C.HotelNumber = D.HotelNumber) AND (C.HotelNumber>0 OR C.LocalHotel=1) AND ISNULL(C.MarkAsDeleted, 0) = 0)

    AND PacketNumber=@APacketNumber
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @PacketNumber, @HotelNumber, @HotelName
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @Result <> ''
      SET @Result = @Result + ','
      
    SET @Result = @Result + @HotelName

    FETCH NEXT FROM db_cur INTO @PacketNumber, @HotelNumber, @HotelName
  END

  RETURN @Result
END

GO


