-- System Params not in formula
@Interject_RequestContext nvarchar(max)
,@TestMode bit = 0 -- used only for testing the stored procedure directly.  It will show more results when set to 1.

CREATED DATE: 3/7/2023 8:22 AM
CREATED BY: Interject Default
DESCRIPTION: Example data pull to be used with Interject

---------------------------------------------------------------------------
TEST Example: Use the below to test this stored procedure:
---------------------------------------------------------------------------

This section has been removed. To get sample code for testing this stored procedure,
select the cell in Excel containing the ReportSave function. Then click on the Interject
ribbon, click System > View SQL Test For ActiveCell.

---------------------------------------------------------------------------
Validation Example:
---------------------------------------------------------------------------
Note: By adding 'UserNotice:' to the error message, Interject will show the message in an alert box and not flag the formula with an error.
Without it, the formula will show an error with the text the user can click into.

IF @ParameterName = 'TheWrongValue'
BEGIN
	RAISERROR ('UserNotice:You selected an invalid value. Unable to continue.',
	18, -- Severity,
	1) -- State)
	
	RETURN
END

---------------------------------------------------------------------------
Reserved Parameters:
---------------------------------------------------------------------------
Note: If the below parameters are added to your stored procedure and to the
data portal parameter list, Interject will pass the related value automatically.

@Interject_XMLDataToSave varchar(max) - Required for saving data. It contains XML for the designated cells values.
@Interject_ColDefItems varchar(max) - Provides the Column Definitions in XML designated within the report formula.
@Interject_RowDefItems varchar(max) - Provides the Row Definitions in XML designated within the report formula.
@Interject_SourceFileAndPath varchar(500) - Provides the path and file name delimited by '|' of the current file.
@Interject_SourceFilePathAndTab varchar(1000) - Provides the path, file name and active tab name delimited by '|' of the current file.
@Interject_LocalTimeZoneOffset money - Provides a number (0.000) that represents the offset of the users time to UTC time.
@Interject_NTLogin varchar(50) - Provides the user's computer login name for their current session.
@Interject_UserID varchar(50) - Provides the Interject User ID for their current session.
@Interject_LoginName varchar(50) - Provides the Interject Full name for their current session.
@Interject_ExcelVersion varchar(50) - Provides the users Excel version.
@Interject_UserRoles varchar(1000) - Provides the Interject roles assigned to the user.
@Interject_ClientID varchar(50) - Provides the Interject Client ID for their current session.
@Interject_ReturnError varchar(2000) - Is an output parameter that can be used to return an error back to the user. Pass empty string for no error.
@Interject_RequestContext nvarchar(max) - Provides all above request context and both the open text and encrypted version of the user context.

--
-- Use helper to extract data from Interject_RequestContext. Remove items that are not needed
-- The helper stored procedure [dbo].[RequestContext_Parse] is required for this section to function
--

DECLARE
	@ExcelVersion					NVARCHAR(100)
	,@IdsVersion					NVARCHAR(100)	
	,@FileName					NVARCHAR(1000)	
	,@FilePath					NVARCHAR(1000)
	,@TabName						NVARCHAR(1000)	
	,@CellRange					NVARCHAR(100)	
	,@SourceFunction				NVARCHAR(100)	
	,@UtcOffset					DECIMAL(6,4)	
	,@ColDefItemsDelimited			xml	
	,@ResultDefItemsDelimited		xml	
	,@RowDefItemsDelimited			xml
	,@MachineLoginName				NVARCHAR(100)
	,@MachineName					NVARCHAR(100)	
	,@Interject_UserID				NVARCHAR(100)
	,@Interject_ClientID			NVARCHAR(100)
	,@Interject_LoginName			NVARCHAR(100)	
	,@Interject_LoginAuthTypeID		INT		
	,@Interject_LoginDateUTC		     DATETIME
	,@Interject_UserRolesDelimited	NVARCHAR(max)
	,@UserContextEncrypted			NVARCHAR(4000)
	,@Interject_XMLDataToSave	     xml	

EXEC [dbo].[RequestContext_Parse]
	@Interject_RequestContext		 = @Interject_RequestContext
	,@ExcelVersion					 = @ExcelVersion				OUTPUT
	,@IdsVersion					 = @IdsVersion					OUTPUT
	,@FileName					 = @FileName					OUTPUT
	,@FilePath					 = @FilePath					OUTPUT
	,@TabName						 = @TabName					OUTPUT
	,@CellRange					 = @CellRange			  		OUTPUT
	,@SourceFunction				 = @SourceFunction		  		OUTPUT
	,@UtcOffset					 = @UtcOffset		   			OUTPUT
	,@ColDefItemsDelimited			 = @ColDefItemsDelimited			OUTPUT
	,@ResultDefItemsDelimited		 = @ResultDefItemsDelimited		OUTPUT
	,@RowDefItemsDelimited			 = @RowDefItemsDelimited			OUTPUT
	,@MachineLoginName				 = @MachineLoginName			OUTPUT
	,@MachineName					 = @MachineName				OUTPUT
	,@Interject_UserID				 = @Interject_UserID			OUTPUT
	,@Interject_ClientID			 = @Interject_ClientID			OUTPUT
	,@Interject_LoginName			 = @Interject_LoginName			OUTPUT
	,@Interject_LoginAuthTypeID		 = @Interject_LoginAuthTypeID		OUTPUT
	,@Interject_LoginDateUTC			 = @Interject_LoginDateUTC		OUTPUT
	,@Interject_UserRolesDelimited	 = @Interject_UserRolesDelimited	OUTPUT
	,@UserContextEncrypted			 = @UserContextEncrypted			OUTPUT
	,@Interject_XMLDataToSave		 = @Interject_XMLDataToSave		OUTPUT

IF @TestMode = 1
BEGIN
	SELECT
		@ExcelVersion					as '@ExcelVersion'
		,@IdsVersion					as '@IdsVersion'
		,@FileName					as '@FileName'
		,@FilePath					as '@FilePath '
		,@TabName						as '@TabName'
		,@CellRange					as '@CellRange'
		,@SourceFunction				as '@SourceFunction'
		,@UtcOffset					as '@UtcOffset'
		,@ColDefItemsDelimited			as '@ColDefItemsDelimited'
		,@ResultDefItemsDelimited		as '@ResultDefItemsDelimited'
		,@RowDefItemsDelimited			as '@RowDefItemsDelimited'
		,@MachineLoginName				as '@MachineLoginName'
		,@MachineName					as '@MachineName'
		,@Interject_UserID				as '@Interject_UserID'
		,@Interject_ClientID			as '@Interject_ClientID'
		,@Interject_LoginName			as '@Interject_LoginName'
		,@Interject_LoginAuthTypeID		as '@Interject_LoginAuthTypeID'
		,@Interject_LoginDateUTC			as '@Interject_LoginDateUTC'
		,@Interject_UserRolesDelimited	as '@Interject_UserRolesDelimited'
		,@UserContextEncrypted			as '@UserContextEncrypted'
		,@Interject_XMLDataToSave		as '@Interject_XMLDataToSave'
END

DECLARE @ErrorMessageToUser as varchar(1000) = ''

--
-- PROCESS THE XML DATA INTO TABLE VARIABLE
-- varchar(max) is used for the data type by default.  Please change for the specific requirement.
--

DECLARE @DataToProcess TABLE
(
	-- the below are system values used internally in this stored procedures
	 [_ExcelRow] INT 
	,[_MessageToUser] VARCHAR(500) DEFAULT('')
	-- Below are your columns expected from the spreadsheet that is calling this save stored procedure
	-- Please change the (max) to the expected length of the text input.  If limiting length, be sure to make the size larger than allowed so you can add validation to notify the user

	,[CustomerID] NCHAR(5)
	,[CompanyName] NVARCHAR(30)
	,[ContactName] NVARCHAR(30)
	,[ContactTitle] NVARCHAR(30)
	,[Phone] NVARCHAR(24)
	,[Phone_Int] INT
	,[City] NVARCHAR(15)
	,[Country] NVARCHAR(15)
	,[ToBeDeleted] NVARCHAR(10)
	,[ToBeDeletedBit] BIT DEFAULT(0)
)

IF DATALENGTH(@Interject_XMLDataToSave) > 0 
BEGIN
	declare @DataToProcessXML as XML
	-- Handle conversion of XML text into an XML variable.  
	BEGIN TRY
		SET @DataToProcessXML = CAST(@Interject_XMLDataToSave as XML)
	END TRY
	BEGIN CATCH
		SET @ErrorMessageToUser = 'Error in Parsing XML from Interject.  Error: ' + ERROR_MESSAGE()
		GOTO FinalResponseToUser
	END CATCH
	
	-- Insert the XML into the table variable for further processing
	INSERT into @DataToProcess(
	    [_ExcelRow]
	    ,[CustomerID]
	    ,[CompanyName]
	    ,[ContactName]
	    ,[ContactTitle]
	    ,[Phone]
	    ,[City]
	    ,[Country]
	    ,[ToBeDeleted]

	)
	SELECT
		-- The below row value is always provided by Interject
		T.c.value('./Row[1]', 'INT') as [_ExcelRow]
		-- The below columns are provided based on your column definitions in the ReportSave() function
		,T.c.value('./CustomerID[1]', 'NCHAR(5)') as [CustomerID]
		,T.c.value('./CompanyName[1]', 'NVARCHAR(30)') as [CompanyName]
		,T.c.value('./ContactName[1]', 'NVARCHAR(30)') as [ContactName]
		,T.c.value('./ContactTitle[1]', 'NVARCHAR(30)') as [ContactTitle]
		,T.c.value('./Phone[1]', 'NVARCHAR(24)') as [Phone]
		,T.c.value('./City[1]', 'NVARCHAR(15)') as [City]
		,T.c.value('./Country[1]', 'NVARCHAR(15)') as [Country]
		,T.c.value('./Delete[1]', 'NVARCHAR(10)') as [ToBeDeleted]
	FROM @DataToProcessXML.nodes('/XMLDataToSave/r') T(c)
END

-- TestMode is provided so a test save can be executed and all related data can be 
-- easily viewed for testing while not effecting any data in the database
IF @TestMode =1 
BEGIN
	SELECT '@DataToProcess - After XML Processing' as ResultName
	SELECT * FROM @DataToProcess
END

UPDATE @DataToProcess 
	SET [ToBeDeletedBit] = 1
WHERE [ToBeDeleted] = 'yes'

--
-- VALIDATION SECTION - Ensure inputs received from Excel are valid
--

-- Validate that the details do not have duplicates on the primary key
UPDATE m
SET [_MessageToUser] = [_MessageToUser] + ', Duplicate key'
FROM @DataToProcess m
INNER JOIN
	(
		SELECT [CustomerID] 
		FROM @DataToProcess
		WHERE [CustomerID] <> ''
		GROUP BY [CustomerID]
		HAVING COUNT(1) > 1
	) as t
	ON
		m.CustomerID = t.CustomerID

-- Now check if there were any validation issues in the detail and stop processing if true
IF EXISTS(SELECT 1 FROM @DataToProcess WHERE [_MessageToUser] <> '')
BEGIN
	SET @ErrorMessageToUser = 'There were errors in the details of your input.  Please review the errors noted in each row.'
	GOTO FinalResponseToUser
END

--
-- DATA UPDATE
--

BEGIN TRY
	
	-- this table variable will log the changes to the target table so it can be used
	-- to return helpful feedback to the user about how the save action resulted
	DECLARE @ChangeLog as TABLE
	(
		[_ExcelRow] INT	-- will capture the source row that affected the target table
		,[UpdateTypeCode] VARCHAR(10)  -- Will show UPDATE, INSERT, DELETE or ADDED_BACK
		,[CustomerID] NCHAR(5)
		,[IsDeleted] BIT
		,[IsDeletedOld] BIT
	)
	
	BEGIN TRAN t1
		--
		--  use MERGE statement that UPDATES, INSERTS, and DELETES in one action
		--
		MERGE dbo.Northwind_Customers t -- t = the target table or view to update
		USING (
			SELECT
			    [_ExcelRow]
			    ,[CustomerID]
			    ,[CompanyName]
			    ,[ContactName]
			    ,[ContactTitle]
			    ,[Phone]
			    ,[City]
			    ,[Country]
			    ,[ToBeDeleted]
			    ,[ToBeDeletedBit]
			FROM @DataToProcess 
		) s -- s = the source data to be used to update the target table
		-- the join on the column keys is specified below
		ON 
			s.[CustomerID] = t.[CustomerID]
			
		WHEN MATCHED -- Handles the update based on INNER JOIN
			AND NOT 
			(
			 -- To ensure only changes are captured
				s.[CompanyName] = t.[CompanyName]
				AND
				s.[ContactName] = t.[ContactName]
				AND
				s.[ContactTitle] = t.[ContactTitle]
				AND
				s.[Phone] = t.[Phone]
				AND
				s.[City] = t.[City]
				AND
				s.[Country] = t.[Country]
				AND
				s.[ToBeDeletedBit] = t.[IsDeleted]
			)
			THEN
			UPDATE SET
			      t.[CompanyName] = s.[CompanyName]
				,t.[ContactName] = s.[ContactName]
				,t.[ContactTitle] = s.[ContactTitle]
				,t.[Phone] = s.[Phone]
				,t.[City] = s.[City]
				,t.[Country] = s.[Country]
				,t.[IsDeleted] = s.[ToBeDeletedBit]   

		 WHEN NOT MATCHED BY TARGET
			AND
			LOWER(s.[ToBeDeleted]) <> 'yes'
			THEN INSERT (
			    [CustomerID]
			    ,[CompanyName]
			    ,[ContactName]
			    ,[ContactTitle]
			    ,[Phone]
			    ,[City]
			    ,[Country]
			    ,[IsDeleted]
			)
			VALUES (
			    [CustomerID]
			    ,[CompanyName]
			    ,[ContactName]
			    ,[ContactTitle]
			    ,[Phone]
			    ,[City]
			    ,[Country]
			    ,0
			)
		-- the output captures the changes to the table and logs to a table variable
		OUTPUT 
			isnull(inserted.[CustomerID],deleted.[CustomerID])  -- include deleted in case delete is added later. Inserted is used for both Update an Insert
			,s.[_ExcelRow] 
			,$action as UpdateTypeCode  -- this logs into an a change log table variable
			,inserted.[IsDeleted]
			,deleted.[IsDeleted] 
		INTO @ChangeLog
		(
			[CustomerID]
			,[_ExcelRow]
			,[UpdateTypeCode]
			,[IsDeleted]
			,[IsDeletedOld]
		); 

	  UPDATE @ChangeLog
	  SET UpdateTypeCode = 'DELETE'
	  WHERE UpdateTypeCode = 'UPDATE'
		 AND [IsDeleted] = 1
		
	  UPDATE @ChangeLog 
	  SET UpdateTypeCode = 'ADDED_BACK'
	  WHERE UpdateTypeCode = 'UPDATE'
		 AND [IsDeleted] = 0
		 AND [IsDeletedOld] = 1

		  IF @TestMode = 1
		BEGIN
		SELECT * FROM dbo.Northwind_Customers
		END

		--now we have enough information to update the message to user for each row
		UPDATE dtp
		SET [_MessageToUser] = 
			CASE cl.UpdateTypeCode
				WHEN 'ADDED_BACK' THEN ', Added!'
				WHEN 'DELETE' THEN ', Deleted!'
				WHEN 'UPDATE' THEN ', Updated!'
				WHEN 'INSERT' THEN ', Inserted!'
				--ELSE ', No change'  -- not used in this example
			END 
		FROM @DataToProcess dtp
			INNER JOIN @ChangeLog cl  -- use Left Join if 'No Change' is to be identified
				 ON dtp.[_ExcelRow] = cl.[_ExcelRow]
			
	IF @TestMode = 1
	BEGIN
		SELECT '@ChangeLog- Show log of changes made' as ResultName
		select * from @ChangeLog
		
		ROLLBACK TRAN t1 -- note this does not roll back changes to table variables, only real tables
		SELECT 'Changes rolled back since in TEST mode!' as TestModeNote
	END
	ELSE
	BEGIN
		COMMIT TRAN t1
	END
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRAN t1
	
	SET @ErrorMessageToUser =  ERROR_MESSAGE()
	GOTO FinalResponseToUser
END CATCH

-- if test mode, show the final table 
IF @TestMode = 1 
BEGIN
	SELECT '@DataToProcess - After Validation' as ResultName
	SELECT * FROM @DataToProcess
END

-- return the recordset results back to the spreadsheet, if needed.
SELECT
	[_ExcelRow] as [Row] -- this relates to the original row of the spreadsheet the data came from
	,SUBSTRING([_MessageToUser],3,1000) as [MessageToUser]  -- This is a field that, if it matches a column in the Results Range, will be placed in that column for the specified row
FROM @DataToProcess

-- if there is an error, raise error and Interject will catch and present to the user.
-- Note that this is specifically done after the above resultset is returned, since initiating an error before
-- will not allow a result set to be returned to provide feedback on each row 
IF @ErrorMessageToUser <> ''
BEGIN
	-- by adding 'UserNotice:' as a prefix to the message, Interject will not consider it a unhandled error 
	-- and will present the error to the user in a message box.
	SET @ErrorMessageToUser = 'UserNotice:' + @ErrorMessageToUser
	
	RAISERROR (@ErrorMessageToUser,
	18, -- Severity,
	1) -- State)
	RETURN
END ```

-- System Params not in formula
@Interject_RequestContext nvarchar(max)
,@TestMode bit = 0 -- used only for testing the stored procedure directly.  It will show more results when set to 1.

CREATED DATE: 3/7/2023 8:22 AM
CREATED BY: Interject Default
DESCRIPTION: Example data pull to be used with Interject

---------------------------------------------------------------------------
TEST Example: Use the below to test this stored procedure:
---------------------------------------------------------------------------

This section has been removed. To get sample code for testing this stored procedure,
select the cell in Excel containing the ReportSave function. Then click on the Interject 
ribbon, click System > View SQL Test For ActiveCell.

---------------------------------------------------------------------------
Validation Example:
---------------------------------------------------------------------------
Note: By adding 'UserNotice:' to the error message, Interject will show the message in an alert box and not flag the formula with an error.
Without it, the formula will show an error with the text the user can click into.

IF @ParameterName = 'TheWrongValue'
BEGIN
	RAISERROR ('UserNotice:You selected an invalid value. Unable to continue.',
	18, -- Severity,
	1) -- State)
	
	RETURN
END

---------------------------------------------------------------------------
Reserved Parameters:
---------------------------------------------------------------------------
Note: If the below parameters are added to your stored procedure and to the
data portal parameter list, Interject will pass the related value automatically.

@Interject_XMLDataToSave varchar(max) - Required for saving data. It contains XML for the designated cells values.
@Interject_ColDefItems  varchar(max) - Provides the Column Definitions in XML designated within the report formula.
@Interject_RowDefItems varchar(max) - Provides the Row Definitions in XML designated within the report formula.
@Interject_SourceFileAndPath varchar(500) - Provides the path and file name delimited by '|' of the current file.
@Interject_SourceFilePathAndTab varchar(1000) - Provides the path, file name and active tab name delimited by '|' of the current file.
@Interject_LocalTimeZoneOffset money - Provides a number (0.000) that represents the offset of the users time to UTC time.
@Interject_NTLogin varchar(50) - Provides the user's computer login name for their current session.
@Interject_UserID varchar(50) - Provides the Interject User ID for their current session.
@Interject_LoginName varchar(50) - Provides the Interject Full name for their current session.
@Interject_ExcelVersion varchar(50) - Provides the users Excel version.
@Interject_UserRoles varchar(1000) - Provides the Interject roles assigned to the user.
@Interject_ClientID varchar(50) - Provides the Interject Client ID for their current session.
@Interject_ReturnError varchar(2000) - Is an output parameter that can be used to return an error back to the user. Pass empty string for no error.
@Interject_RequestContext nvarchar(max) - Provides all above request context and both the open text and encrypted version of the user context.

--
-- Use helper to extract data from Interject_RequestContext. Remove items that are not needed
-- The helper stored procedure [dbo].[RequestContext_Parse] is required for this section to function 
--

DECLARE
	@ExcelVersion					NVARCHAR(100)
	,@IdsVersion					NVARCHAR(100)	
	,@FileName					NVARCHAR(1000)	
	,@FilePath					NVARCHAR(1000)
	,@TabName						NVARCHAR(1000)	
	,@CellRange					NVARCHAR(100)	
	,@SourceFunction				NVARCHAR(100)	
	,@UtcOffset					DECIMAL(6,4)	
	,@ColDefItemsDelimited			xml	
	,@ResultDefItemsDelimited		xml	
	,@RowDefItemsDelimited			xml
	,@MachineLoginName				NVARCHAR(100)
	,@MachineName					NVARCHAR(100)	
	,@Interject_UserID				NVARCHAR(100)
	,@Interject_ClientID			NVARCHAR(100)
	,@Interject_LoginName			NVARCHAR(100)	
	,@Interject_LoginAuthTypeID		INT		
	,@Interject_LoginDateUTC		     DATETIME
	,@Interject_UserRolesDelimited	NVARCHAR(max)
	,@UserContextEncrypted			NVARCHAR(4000)
	,@Interject_XMLDataToSave	     xml	

EXEC [dbo].[RequestContext_Parse]
	@Interject_RequestContext		 = @Interject_RequestContext
	,@ExcelVersion					 = @ExcelVersion				OUTPUT
	,@IdsVersion					 = @IdsVersion					OUTPUT
	,@FileName					 = @FileName					OUTPUT
	,@FilePath					 = @FilePath					OUTPUT
	,@TabName						 = @TabName					OUTPUT
	,@CellRange					 = @CellRange			  		OUTPUT
	,@SourceFunction				 = @SourceFunction		  		OUTPUT
	,@UtcOffset					 = @UtcOffset		   			OUTPUT
	,@ColDefItemsDelimited			 = @ColDefItemsDelimited			OUTPUT
	,@ResultDefItemsDelimited		 = @ResultDefItemsDelimited		OUTPUT
	,@RowDefItemsDelimited			 = @RowDefItemsDelimited			OUTPUT
	,@MachineLoginName				 = @MachineLoginName			OUTPUT
	,@MachineName					 = @MachineName				OUTPUT
	,@Interject_UserID				 = @Interject_UserID			OUTPUT
	,@Interject_ClientID			 = @Interject_ClientID			OUTPUT
	,@Interject_LoginName			 = @Interject_LoginName			OUTPUT
	,@Interject_LoginAuthTypeID		 = @Interject_LoginAuthTypeID		OUTPUT
	,@Interject_LoginDateUTC			 = @Interject_LoginDateUTC		OUTPUT
	,@Interject_UserRolesDelimited	 = @Interject_UserRolesDelimited	OUTPUT
	,@UserContextEncrypted			 = @UserContextEncrypted			OUTPUT
	,@Interject_XMLDataToSave		 = @Interject_XMLDataToSave		OUTPUT

IF @TestMode = 1
BEGIN
	SELECT
		@ExcelVersion					as '@ExcelVersion'
		,@IdsVersion					as '@IdsVersion'
		,@FileName					as '@FileName'
		,@FilePath					as '@FilePath '
		,@TabName						as '@TabName'
		,@CellRange					as '@CellRange'
		,@SourceFunction				as '@SourceFunction'
		,@UtcOffset					as '@UtcOffset'
		,@ColDefItemsDelimited			as '@ColDefItemsDelimited'
		,@ResultDefItemsDelimited		as '@ResultDefItemsDelimited'
		,@RowDefItemsDelimited			as '@RowDefItemsDelimited'
		,@MachineLoginName				as '@MachineLoginName'
		,@MachineName					as '@MachineName'
		,@Interject_UserID				as '@Interject_UserID'
		,@Interject_ClientID			as '@Interject_ClientID'
		,@Interject_LoginName			as '@Interject_LoginName'
		,@Interject_LoginAuthTypeID		as '@Interject_LoginAuthTypeID'
		,@Interject_LoginDateUTC			as '@Interject_LoginDateUTC'
		,@Interject_UserRolesDelimited	as '@Interject_UserRolesDelimited'
		,@UserContextEncrypted			as '@UserContextEncrypted'
		,@Interject_XMLDataToSave		as '@Interject_XMLDataToSave'
END

DECLARE @ErrorMessageToUser as varchar(1000) = ''

--
-- PROCESS THE XML DATA INTO TABLE VARIABLE
-- varchar(max) is used for the data type by default.  Please change for the specific requirement.
--

DECLARE @DataToProcess TABLE
(
	-- the below are system values used internally in this stored procedures
	 [_ExcelRow] INT 
	,[_MessageToUser] VARCHAR(500) DEFAULT('')
	-- Below are your columns expected from the spreadsheet that is calling this save stored procedure
	-- Please change the (max) to the expected length of the text input.  If limiting length, be sure to make the size larger than allowed so you can add validation to notify the user

	,[CustomerID] NCHAR(5)
	,[CompanyName] NVARCHAR(30)
	,[ContactName] NVARCHAR(30)
	,[ContactTitle] NVARCHAR(30)
	,[Phone] NVARCHAR(24)
	,[Phone_Int] INT
	,[City] NVARCHAR(15)
	,[Country] NVARCHAR(15)
	,[ToBeDeleted] NVARCHAR(10)
	,[ToBeDeletedBit] BIT DEFAULT(0)
)

IF DATALENGTH(@Interject_XMLDataToSave) > 0 
BEGIN
	declare @DataToProcessXML as XML
	-- Handle conversion of XML text into an XML variable.  
	BEGIN TRY
		SET @DataToProcessXML = CAST(@Interject_XMLDataToSave as XML)
	END TRY
	BEGIN CATCH
		SET @ErrorMessageToUser = 'Error in Parsing XML from Interject.  Error: ' + ERROR_MESSAGE()
		GOTO FinalResponseToUser
	END CATCH
	
	-- Insert the XML into the table variable for further processing
	INSERT into @DataToProcess(
	    [_ExcelRow]
	    ,[CustomerID]
	    ,[CompanyName]
	    ,[ContactName]
	    ,[ContactTitle]
	    ,[Phone]
	    ,[City]
	    ,[Country]
	    ,[ToBeDeleted]
	)
	SELECT
		-- The below row value is always provided by Interject
		T.c.value('./Row[1]', 'INT') as [_ExcelRow]
		-- The below columns are provided based on your column definitions in the ReportSave() function
		,T.c.value('./CustomerID[1]', 'NCHAR(5)') as [CustomerID]
		,T.c.value('./CompanyName[1]', 'NVARCHAR(30)') as [CompanyName]
		,T.c.value('./ContactName[1]', 'NVARCHAR(30)') as [ContactName]
		,T.c.value('./ContactTitle[1]', 'NVARCHAR(30)') as [ContactTitle]
		,T.c.value('./Phone[1]', 'NVARCHAR(24)') as [Phone]
		,T.c.value('./City[1]', 'NVARCHAR(15)') as [City]
		,T.c.value('./Country[1]', 'NVARCHAR(15)') as [Country]
		,T.c.value('./Delete[1]', 'NVARCHAR(10)') as [ToBeDeleted]
	FROM @DataToProcessXML.nodes('/XMLDataToSave/r') T(c)
END

-- TestMode is provided so a test save can be executed and all related data can be 
-- easily viewed for testing while not effecting any data in the database
IF @TestMode =1 
BEGIN
	SELECT '@DataToProcess - After XML Processing' as ResultName
	SELECT * FROM @DataToProcess
END

UPDATE @DataToProcess 
	SET [ToBeDeletedBit] = 1
WHERE [ToBeDeleted] = 'yes'

--
-- VALIDATION SECTION - Ensure inputs received from Excel are valid
--

-- Validate that the details do not have duplicates on the primary key
UPDATE m
SET [_MessageToUser] = [_MessageToUser] + ', Duplicate key'
FROM @DataToProcess m
INNER JOIN
	(
		SELECT [CustomerID] 
		FROM @DataToProcess
		WHERE [CustomerID] <> ''
		GROUP BY [CustomerID]
		HAVING COUNT(1) > 1
	) as t
	ON
		m.CustomerID = t.CustomerID

-- Now check if there were any validation issues in the detail and stop processing if true
IF EXISTS(SELECT 1 FROM @DataToProcess WHERE [_MessageToUser] <> '')
BEGIN
	SET @ErrorMessageToUser = 'There were errors in the details of your input.  Please review the errors noted in each row.'
	GOTO FinalResponseToUser
END
 
--
-- DATA UPDATE
--

BEGIN TRY
   DECLARE @CurrentDateTime DATETIME2 = GETUTCDATE()
	
	-- this table variable will log the changes to the target table so it can be used
	-- to return helpful feedback to the user about how the save action resulted
	DECLARE @ChangeLog as TABLE
	(
		 --[ChangeLogID] INT IDENTITY(1,1) NOT NULL
		[_ExcelRow] INT	-- will capture the source row that affected the target table
		,[UpdateTypeCode] VARCHAR(10)  -- Will show UPDATE, INSERT, or DELETE
		,[CustomerID] NCHAR(5)
		,[CompanyName] NVARCHAR(40)
		,[CompanyNameOld] NVARCHAR(40)
		,[ContactName] NVARCHAR(30)
		,[ContactNameOld] NVARCHAR(30)
		,[ContactTitle] NVARCHAR(30)
		,[ContactTitleOld] NVARCHAR(30)
		,[Phone] NVARCHAR(24)
		,[PhoneOld] NVARCHAR(24)
		,[City] NVARCHAR(15)
		,[CityOld] NVARCHAR(15)
		,[Country] NVARCHAR(15)
		,[CountryOld] NVARCHAR(15)
		,[EditedBy] NVARCHAR(40) NOT NULL
		,[LastDateEdited] DATETIME2(7) NOT NULL
		,[IsDeleted] BIT
		,[IsDeletedOld] BIT
	)
	
	BEGIN TRAN t1
		--
		--  use MERGE statement that UPDATES and INSERTS in one action
		--
		MERGE dbo.Northwind_Customers t -- t = the target table or view to update
		USING (
			SELECT
			    [_ExcelRow]
			    ,[CustomerID]
			    ,[CompanyName]
			    ,[ContactName]
			    ,[ContactTitle]
			    ,[Phone]
			    ,[City]
			    ,[Country]
			    ,[ToBeDeleted]
			    ,[ToBeDeletedBit]
			FROM @DataToProcess 
		) s -- s = the source data to be used to update the target table
		-- the join on the column keys is specified below
		ON 
			s.[CustomerID] = t.[CustomerID]
			
		WHEN MATCHED -- Handles the update based on INNER JOIN
			AND NOT 
			(
			 -- To ensure only changes are captured
				s.[CompanyName] = t.[CompanyName]
				AND
				s.[ContactName] = t.[ContactName]
				AND
				s.[ContactTitle] = t.[ContactTitle]
				AND
				s.[Phone] = t.[Phone]
				AND
				s.[City] = t.[City]
				AND
				s.[Country] = t.[Country]
				AND
				s.[ToBeDeletedBit] = t.[IsDeleted]
			)
			THEN
			UPDATE SET
			      t.[CompanyName] = s.[CompanyName]
				,t.[ContactName] = s.[ContactName]
				,t.[ContactTitle] = s.[ContactTitle]
				,t.[Phone] = s.[Phone]
				,t.[City] = s.[City]
				,t.[Country] = s.[Country]
				,t.[LastDateEdited] = @CurrentDateTime
				,t.[EditedBy] = @MachineLoginName
				,t.[UpdateTypeCode] = 'UPDATE'
				,t.[IsDeleted] = s.[ToBeDeletedBit]   

		 WHEN NOT MATCHED BY TARGET
			AND
			LOWER(s.[ToBeDeleted]) <> 'yes'
			THEN INSERT (
			    [CustomerID]
			    ,[CompanyName]
			    ,[ContactName]
			    ,[ContactTitle]
			    ,[Phone]
			    ,[City]
			    ,[Country]
			    ,[IsDeleted]
			    ,[EditedBy]
			    ,[UpdateTypeCode]
			    ,[LastDateEdited]
			)
			VALUES (
			    [CustomerID]
			    ,[CompanyName]
			    ,[ContactName]
			    ,[ContactTitle]
			    ,[Phone]
			    ,[City]
			    ,[Country]
			    ,0
			    ,@MachineLoginName
			    ,'INSERT'
			    ,@CurrentDateTime
			)
		-- the output captures the changes to the table and logs to a table variable
		OUTPUT 
			isnull(inserted.[CustomerID],deleted.[CustomerID])  -- include deleted in case delete is added later. Inserted is used for both Update an Insert
			,s.[_ExcelRow] 
			,$action as UpdateTypeCode  -- this logs into an a change log table variable
			,s.[CompanyName]
			,s.[ContactName]
			,s.[ContactTitle]
			,s.[Phone]
			,s.[City]
			,s.[Country]
			,deleted.[CompanyName]
			,deleted.[ContactName]
			,deleted.[ContactTitle]
			,deleted.[Phone]
			,deleted.[City]
			,deleted.[Country]
			,@MachineLoginName
			,@CurrentDateTime
			,inserted.[IsDeleted]
			,deleted.[IsDeleted] 
		INTO @ChangeLog
		(
			[CustomerID]
			,[_ExcelRow]
			,[UpdateTypeCode]
			,[CompanyName]
			,[ContactName]
			,[ContactTitle]
			,[Phone]
			,[City]
			,[Country]
			,[CompanyNameOld]
			,[ContactNameOld]
			,[ContactTitleOld]
			,[PhoneOld]
			,[CityOld]
			,[CountryOld]
			,[EditedBy]
			,[LastDateEdited]
			,[IsDeleted]
			,[IsDeletedOld]
		); 

	  UPDATE @ChangeLog
	  SET UpdateTypeCode = 'DELETE'
	  WHERE UpdateTypeCode = 'UPDATE'
		 AND [IsDeleted] = 1
		
	  UPDATE @ChangeLog 
	  SET UpdateTypeCode = 'ADDED_BACK'
	  WHERE UpdateTypeCode = 'UPDATE'
		 AND [IsDeleted] = 0
		 AND [IsDeletedOld] = 1

	  UPDATE [demo].[Northwind_Customers]
	  SET [demo].[Northwind_Customers].UpdateTypeCode = cl.UpdateTypeCode
	  FROM [demo].[Northwind_Customers] nc
	  INNER JOIN @ChangeLog cl
	  ON nc.CustomerID = cl.CustomerID
   
		  IF @TestMode = 1
		BEGIN
		SELECT * FROM dbo.Northwind_Customers
		END

		 -- Export the ChangeLog records to the History table
		 INSERT INTO dbo.Northwind_Customers_History (
			[CustomerID]
			,[CompanyName]
			,[CompanyNameOld]
			,[ContactName]
			,[ContactNameOld]
			,[ContactTitle]
			,[ContactTitleOld]
			,[Phone]
			,[PhoneOld]
			,[City]
			,[CityOld]
			,[Country]
			,[CountryOld]
			,[EditedBy]
			,[ActionType]
			,[DateEdited]
		 )
		 SELECT
			cl.[CustomerID]
			,cl.[CompanyName]
			,cl.[CompanyNameOld]
			,cl.[ContactName]
			,cl.[ContactNameOld]
			,cl.[ContactTitle]
			,cl.[ContactTitleOld]
			,cl.[Phone]
			,cl.[PhoneOld]
			,cl.[City]
			,cl.[CityOld]
			,cl.[Country]
			,cl.[CountryOld]
			,cl.[EditedBy]
			,cl.[UpdateTypeCode]
			,cl.[LastDateEdited]
		 FROM @ChangeLog cl
		 WHERE cl.UpdateTypeCode IS NOT NULL

		--now we have enough information to update the message to user for each row
		UPDATE dtp
		SET [_MessageToUser] = 
			CASE cl.UpdateTypeCode
				WHEN 'ADDED_BACK' THEN ', Added!'
				WHEN 'DELETE' THEN ', Deleted!'
				WHEN 'UPDATE' THEN ', Updated!'
				WHEN 'INSERT' THEN ', Inserted!'
				--ELSE ', No change'  -- not used in this example
			END 
		FROM @DataToProcess dtp
			INNER JOIN @ChangeLog cl  -- use Left Join if 'No Change' is to be identified
				 ON dtp.[_ExcelRow] = cl.[_ExcelRow]
			
	IF @TestMode = 1
	BEGIN
		SELECT '@ChangeLog- Show log of changes made' as ResultName
		select * from @ChangeLog
		
		ROLLBACK TRAN t1 -- note this does not roll back changes to table variables, only real tables
		SELECT 'Changes rolled back since in TEST mode!' as TestModeNote
	END
	ELSE
	BEGIN
		COMMIT TRAN t1
	END
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRAN t1
	
	SET @ErrorMessageToUser =  ERROR_MESSAGE()
	GOTO FinalResponseToUser
END CATCH

-- if test mode, show the final table 
IF @TestMode = 1 
BEGIN
	SELECT '@DataToProcess - After Validation' as ResultName
	SELECT * FROM @DataToProcess
END

-- return the recordset results back to the spreadsheet, if needed.
SELECT
	[_ExcelRow] as [Row] -- this relates to the original row of the spreadsheet the data came from
	,SUBSTRING([_MessageToUser],3,1000) as [MessageToUser]  -- This is a field that, if it matches a column in the Results Range, will be placed in that column for the specified row
FROM @DataToProcess

-- if there is an error, raise error and Interject will catch and present to the user.
-- Note that this is specifically done after the above resultset is returned, since initiating an error before
-- will not allow a result set to be returned to provide feedback on each row 
IF @ErrorMessageToUser <> ''
BEGIN
	-- by adding 'UserNotice:' as a prefix to the message, Interject will not consider it a unhandled error 
	-- and will present the error to the user in a message box.
	SET @ErrorMessageToUser = 'UserNotice:' + @ErrorMessageToUser
	
	RAISERROR (@ErrorMessageToUser,
	18, -- Severity,
	1) -- State)
	RETURN
END ```