usp_Import_Calls_Received_Disposition.sql

USE [Call_DW]
GO

/****** Object:  StoredProcedure [Import].[usp_Import_Calls_Received_Disposition]    Script Date: 9/16/2021 4:24:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('Import.usp_Import_Calls_Received_Disposition', 'P') IS NOT NULL
    DROP PROCEDURE Import.usp_Import_Calls_Received_Disposition
GO

CREATE PROCEDURE [Import].[usp_Import_Calls_Received_Disposition]
(
	@json NVARCHAR(MAX)
)
AS
-- =============================================
-- Author:		Bill Kea
-- Create date: 08/21/2021
-- Description:	Imports JSON documents into Import_CallsReceived_Disposition table
-- =============================================
BEGIN
/*
	DEFINE LOCAL VARIABLES
*/
DECLARE @error_number AS INT
DECLARE @error_state AS INT
DECLARE @error_severity AS INT
DECLARE @error_procedure AS NVARCHAR(120)
DECLARE @error_line AS INT
DECLARE @error_message NVARCHAR(4000) 

DECLARE @error_cnt AS INT = 0;
DECLARE @error_max AS INT = 3;

	/*
	BUILD OUT ERROR TABLE
	*/
SELECT * INTO #DeadLockErrors FROM master.dbo.sysmessages WHERE description LIKE '%DEADLOCK%' AND msglangid = 1033;


RETRYINSERT:
BEGIN TRY
	IF (ISJSON(@json) = 0) 	
	BEGIN
		RAISERROR ('Invalid or empty Calls Received JSON document',11,1) WITH NOWAIT;
	END


	SELECT
		IvrId,
		GeneralInfo_CallId,
		Disposition_ClientType,
		Disposition_ClientTypeRef1,
		Disposition_ClientTypeRef2,
		Disposition_ClientTypeRef3,
		Disposition_MainSubject,
		Disposition_Subsubject,
		Disposition_SubsubjectDetails,
		Disposition_Resolution,
		Disposition_IsFlagged,
		Disposition_FlagingReason,
		Disposition_Notes,
		Disposition_CreationDate,
		Disposition_CreatedBy
	INTO
		#CallsReceived_Disposition
	FROM OPENJSON(@json)
		WITH (
		IvrId INT '$.IvrId',
		GeneralInfo_CallId VARCHAR(20) '$.GeneralInfo.CallId',
		Disposition_ClientType VARCHAR(20) '$.Disposition.ClientType',
		Disposition_ClientTypeRef1 VARCHAR(20) '$.Disposition.ClientTypeRef1',
		Disposition_ClientTypeRef2 VARCHAR(20) '$.Disposition.ClientTypeRef2',
		Disposition_ClientTypeRef3 VARCHAR(20) '$.Disposition.ClientTypeRef3',
		Disposition_MainSubject VARCHAR(20) '$.Disposition.MainSubject',
		Disposition_Subsubject VARCHAR(20) '$.Disposition.Subsubject',
		Disposition_SubsubjectDetails VARCHAR(20) '$.Disposition.SubsubjectDetails',
		Disposition_Resolution VARCHAR(20) '$.Disposition.Resolution',
		Disposition_IsFlagged BIT '$.Disposition.IsFlagged',
		Disposition_FlagingReason VARCHAR(20) '$.Disposition.FlagingReason',
		Disposition_Notes VARCHAR(20) '$.Disposition.Notes',
		Disposition_CreationDate DATETIME '$.Disposition.CreationDate',
		Disposition_CreatedBy INT '$.Disposition.CreatedBy'
		)
	WHERE
		LEN(RTRIM(Disposition_ClientType)) > 0;

	BEGIN TRAN
		INSERT INTO
			[Import].[CallsReceived_Disposition]
			(
				IvrId,
				GeneralInfo_CallId,
				Disposition_ClientType,
				Disposition_ClientTypeRef1,
				Disposition_ClientTypeRef2,
				Disposition_ClientTypeRef3,
				Disposition_MainSubject,
				Disposition_Subsubject,
				Disposition_SubsubjectDetails,
				Disposition_Resolution,
				Disposition_IsFlagged,
				Disposition_FlagingReason,
				Disposition_Notes,
				Disposition_CreationDate,
				Disposition_CreatedBy
			)
		SELECT
			IvrId,
			GeneralInfo_CallId,
			Disposition_ClientType,
			Disposition_ClientTypeRef1,
			Disposition_ClientTypeRef2,
			Disposition_ClientTypeRef3,
			Disposition_MainSubject,
			Disposition_Subsubject,
			Disposition_SubsubjectDetails,
			Disposition_Resolution,
			Disposition_IsFlagged,
			Disposition_FlagingReason,
			Disposition_Notes,
			Disposition_CreationDate,
			Disposition_CreatedBy
		FROM
			#CallsReceived_Disposition
		EXCEPT
		SELECT
			IvrId,
			GeneralInfo_CallId,
			Disposition_ClientType,
			Disposition_ClientTypeRef1,
			Disposition_ClientTypeRef2,
			Disposition_ClientTypeRef3,
			Disposition_MainSubject,
			Disposition_Subsubject,
			Disposition_SubsubjectDetails,
			Disposition_Resolution,
			Disposition_IsFlagged,
			Disposition_FlagingReason,
			Disposition_Notes,
			Disposition_CreationDate,
			Disposition_CreatedBy
		FROM
			[Import].[CallsReceived_Disposition]
	COMMIT TRAN

END TRY
BEGIN CATCH
	ROLLBACK TRAN
		
--  Determine if this is a deadlock issue
	IF EXISTS(SELECT error FROM #DeadLockErrors WHERE error = ERROR_NUMBER()) AND (@error_cnt < @error_max)
	BEGIN
		SET @error_cnt += 1;		--increment error loop counter
		WAITFOR DELAY '00:00:05';	--wait 5 milliseconds
		GOTO RETRYINSERT;			--retry transaction
	END
	
--  Log Error into error table
	--INSERT INTO dbo.DB_Errors(err_number,err_state,err_severity,err_procedure,err_line,err_message,err_dt)
	--VALUES(ERROR_NUMBER(),ERROR_STATE(),ERROR_SEVERITY(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(),GETDATE());

	SELECT 
		@error_message = ERROR_MESSAGE()
		,@error_severity = ERROR_SEVERITY()
		,@error_state = ERROR_STATE()

	RAISERROR (@error_message,@error_severity,@error_state) WITH NOWAIT;  
END CATCH

END
GO