CREATE FUNCTION [dbo].[HotelCustomerNumbersFromHotelNumbers] (@HotelNumberStr VARCHAR(500))
/*

  Created by Kent Johansen November 2006
  Changes:
    KJ 20100624: Bug: Fix for "Handle deleted customers/Hotels"
*/

RETURNS @HotelNumbers TABLE (
  CustomerNumber INT NOT NULL,
  HotelNumber INT NOT NULL
) AS

BEGIN
  IF @HotelNumberStr <> ''
  BEGIN
    DECLARE @HotelNumber VARCHAR(10)
    DECLARE @CustomerNumber INT
    DECLARE @Pos INT

    SET @HotelNumberStr = LTRIM(RTRIM(@HotelNumberStr))+ ','
    SET @Pos = CHARINDEX(',', @HotelNumberStr, 1)

    IF REPLACE(@HotelNumberStr, ',', '') <> ''
    BEGIN
      WHILE @Pos > 0
      BEGIN
        SET @HotelNumber = LTRIM(RTRIM(LEFT(@HotelNumberStr, @Pos - 1)))
        IF @HotelNumber <> ''
        BEGIN 
          SET @CustomerNumber = 0
          SELECT @CustomerNumber = ISNULL(CustomerNumber, 0) FROM Customer WITH (NOLOCK) WHERE HotelNumber = @HotelNumber AND CustomerTypeNumber = 7 AND ISNULL(MarkAsDeleted, 0) = 0
          IF @CustomerNumber > 0
            INSERT INTO @HotelNumbers VALUES (@CustomerNumber, @HotelNumber) 
        END
        SET @HotelNumberStr = RIGHT(@HotelNumberStr, LEN(@HotelNumberStr) - @Pos)
        SET @Pos = CHARINDEX(',', @HotelNumberStr, 1)
      END
    END
  END

  RETURN
END