CREATE PROCEDURE [dbo].[OnlineFindLocationsExt]
AS

-- =============================================
-- Author:		Ken Sørensen
-- Create date: June 2016
-- Description:	Retrives the locations including hotelnumber
-- Changes:
/*
   KS 20160629  SortNumber added.
   KS 20160705  Joined on ID instead of ExternalID.
*/
-- =============================================

DECLARE @Rows INT
DECLARE @CustomerNumber INT
DECLARE @LocationName VARCHAR (50)
DECLARE @Id INT
DECLARE @ExternalId INT
DECLARE @SortNumber INT
DECLARE @HotelNumber INT

CREATE TABLE #TempLocations (
	[ExternalID] [int] NOT NULL,
	[LocationName] [VARCHAR] (50) NOT NULL,
	[HotelNumber] [int] NOT NULL,
	[SortNumber] [int] NULL)
ON  [PRIMARY]

DECLARE #InitTempLocations CURSOR FAST_FORWARD FOR
SELECT ID, ExternalId, LocationName, SortNumber FROM Location (NOLOCK)

OPEN #InitTempLocations
FETCH NEXT FROM #InitTempLocations
INTO @ID, @ExternalID, @LocationName, @SortNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN	
	SET @CustomerNumber = 0
	SET @HotelNumber = 0

	SELECT @HotelNumber = C.HotelNumber, @CustomerNumber = C.CustomerNumber FROM Customer C (NOLOCK)
	INNER JOIN LocationCustomerXTab (NOLOCK) X on X.FKCustomerNumber = C.CustomerNumber
	WHERE X.FKLocationID = @ID

	IF @CustomerNumber <> 0
	BEGIN
		SELECT @Rows = COUNT(CityOverwrite) FROM Customer (NOLOCK)
		WHERE CityOverwrite = @LocationName

		IF @Rows > 0 
			SET @HotelNumber = -1

		INSERT INTO #TempLocations VALUES (@ExternalID, @LocationName, @HotelNumber, @SortNumber)
	END

	FETCH NEXT FROM #InitTempLocations
	INTO @ID, @ExternalID, @LocationName, @SortNumber
END

CLOSE #InitTempLocations
DEALLOCATE #InitTempLocations

SELECT * FROM #TempLocations (NOLOCK)
ORDER BY SortNumber
DROP TABLE #TempLocations
