CREATE PROCEDURE [dbo].[GPISetHotelSetupData](@SearchKey VARCHAR(60), @HotelNumber INTEGER, @Value VARCHAR(768),  @Result BIT OUT)
 AS
BEGIN 
/*
	Created 20190504 By Carsten Cremer.
	Purpose : Set a value in the HotelSetuo table, either as definded per hotel or the general value common for all hotels.
	          if the key does not exists yet, create a new key, and insert the default value into it.

        CC 20200609 Change of logic. Svend needs to be able to insert a new key straight into a hotel specific key thus 
			    changing the logic so that the first value added not necessarily is a common value for the entire chain.
			    Since a common value still needs to be created in this case it is set to blank - NOT NULL.

	TestBlock:
	DECLARE @RESULT BIT;
	SET @Result = 0;
	EXECUTE GPISetHotelSetupData 'Interface.DummyInterface',0,'DummyData', @Result OUT
	PRINT @Result;
*/

  DECLARE @HotelCustomerNumber integer;
  DECLARE @lRowCount integer;             -- Don't know if the volatility of @@rowcount is a bug or feature in T-SQL but without this QDF th e logic collapses 
  DECLARE @StoredValue VARCHAR(768) = '';

  SET     @Result = 0;        -- 
  SET     @HotelCustomerNumber = ISNULL( (SELECT CustomerNumber FROM Customer WITH(NOLOCK) WHERE ( (CustomerTypeNumber = 7) AND (HotelNumber = @HotelNumber) AND ISNULL( MARKASDELETED, 0) = 0)) , 0);
  SET	  @lRowCount = 0;
  
  -- PRINT 'HotelCustomerNumber :' + CAST( @HotelCustomerNumber AS VARCHAR(10));

  BEGIN -- First make sure that the key exists in the regular hotel setup table regardless of whether the function is called with or without hotelnumber.
    SELECT  KeyValue FROM HotelSetup WITH( NOLOCK ) WHERE SearchKey =@SearchKey ; 
	SET @lRowCount = @@rowcount;	
    -- PRINT 'Key exists :' + CAST( @lRowCount AS VARCHAR(10));
	IF (@lRowCount = 0) 
	-- Streamlining : Insert a blank key regardless of whether you'll update it further down
		INSERT INTO HotelSetup (SearchKey,KeyValue) VALUES (@SearchKey,'' ); 
  END


  IF @HotelCustomerNumber > 0
  BEGIN
    UPDATE HotelSetupValue SET KeyValue = @Value
	WHERE HotelSetupValue.PK_ValueNumber= ( SELECT HotelSetupValueXTab.FK_ValueNumber FROM HotelSetupValueXTab WITH(NOLOCK)
	WHERE HotelSetupValueXTab.CustomerNumber = @HotelCustomerNumber
	  AND HotelSetupValueXTab.HotelSetupNumber =
	   (SELECT HotelSetupNumber FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE @SearchKey))
    SET @lRowCount = @@rowcount;
--    PRINT 'XKey exists :' + CAST( @lRowCount AS VARCHAR(10));
   
    IF (@lRowCount =0)
    BEGIN   
        SET NOCOUNT ON; 
        DECLARE @HSNumb integer; 
        SET @HSNumb = (SELECT HotelSetupNumber FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = @SearchKey);  

        INSERT INTO HotelSetupValue ( KeyValue ) VALUES ( @Value); 
        INSERT INTO HotelSetupValueXTab ( HotelSetupNumber, CustomerNumber, FK_ValueNumber ) 
        VALUES ( @HSNumb,@HotelCustomerNumber, @@IDENTITY )  ;
	    SET @lRowCount = @@rowcount;
--	    PRINT 'XKey created :' + CAST( @lRowCount AS VARCHAR(10));
    END
  END

  IF (@lRowCount = 0) OR (@HotelCustomerNumber < 1)
  BEGIN
   UPDATE HotelSetup SET KEYVALUE = @Value WHERE SearchKey LIKE @SearchKey 
   SET @lRowCount = @@rowcount;
  END

 IF (@lRowCount > 0) 
   SET @Result = 1;
   
 
 
--  PRINT 'Result :' + CAST( @Result AS VARCHAR(10));
 
END