﻿-- =============================================
-- Description:	<Json daraboló a dinamikus importhoz>
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[sp_DynamicImportJsonSplit]') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[sp_DynamicImportJsonSplit]
END
GO

CREATE PROCEDURE [dbo].[sp_DynamicImportJsonSplit]
	 @pImportJson			NVARCHAR(MAX)
	,@pIsTorles				BIT
	,@pTableNameList		NVARCHAR(2000)
	,@pInsertAlways			BIT

AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @OrderTable TABLE (	
		 Number		INT
		,TableName	NVARCHAR(50)
	)

	--Paraméterként kapott táblanevek beszúrása
	INSERT INTO 
		@OrderTable 
	SELECT * 
	FROM (
		SELECT 
			 ROW_NUMBER() OVER(ORDER BY [value] DESC)	Number
			,VALUE										TableName
		FROM
			STRING_SPLIT (@pTableNameList, ',')
		WHERE 
			RTRIM(value) <> '') AS subSelect
	DECLARE 
		 @number		INT = 1
		,@tableName		NVARCHAR(50)
		,@actualJson	NVARCHAR(MAX)

	WHILE EXISTS(SELECT TableName FROM @OrderTable WHERE Number = @number)
		BEGIN
		     SELECT 
				@tableName = TableName 
			FROM
				@OrderTable
			WHERE 
				Number = @number

			IF EXISTS(SELECT [Key] FROM OPENJSON(@pImportJson, N'strict $.ImportJson') WHERE [Key] = @tableName)
				BEGIN			
					--[dbo].[sp_DynamicImport] meghívása a JSON megfelelő részével
					SET @actualJson = CONVERT(NVARCHAR(max), (
						SELECT 
							[value]
						FROM 
							OPENJSON(@pImportJson, N'strict $.ImportJson')
						WHERE 
							[Key] =  @tableName)
					)	 
							 
					SET @actualJson = '{"ImportJson": {"' + @tableName + '":' + @actualJson + '} }'

					EXECUTE [dbo].[sp_DynamicImport] @pImportJson = @actualJson, @pIsTorles = @pIsTorles, @pInsertAlways = @pInsertAlways					
				END

		  SET @number = @number + 1;
		END

END


GO

