CREATE PROCEDURE MIG_CATEGORY_Sp
AS 
BEGIN

	Declare @CAT_ID NVARCHAR(max),  
			@sqlQuery nvarchar(max), 
			@sqlQueryInsert nvarchar(max), 
			@res BIT, @CAT_LEVEL INT
			
	SET @CAT_ID =''
	SET @CAT_LEVEL = 0
	--SET @sqlQuery = N'if exists(SELECT CATEGORY_ID FROM CATEGORY_TREE WHERE PARENT_ID IN (@CATEGORY_ID) ) set @res = 1 else set @res = 0'
	--SET @sqlQueryInsert = N'SELECT CATEGORY_ID, @CATEGORY_LEVEL as [LEVEL] FROM CATEGORY_TREE WHERE CATEGORY_ID IN (@CATEGORY_ID)'

	DELETE FROM MIG_CATEGORY

	IF OBJECT_ID(N'tempdb..#TMP_CATEGORY') IS NOT NULL
	BEGIN
		DROP TABLE #TMP_CATEGORY
	END
	SELECT CATEGORY_ID INTO #TMP_CATEGORY FROM CATEGORY_TREE WHERE PARENT_ID = ''

	WHILE (SELECT COUNT(CATEGORY_ID) FROM #TMP_CATEGORY) >0
	BEGIN
		SET @CAT_LEVEL = @CAT_LEVEL +1
	
		
		IF EXISTS(SELECT CAT.CATEGORY_ID 
						 FROM CATEGORY_TREE CAT 
						 JOIN #TMP_CATEGORY TMP 
						 ON CAT.CATEGORY_ID = TMP.CATEGORY_ID
				) 
		BEGIN
				--SELECT CATEGORY_ID FROM CATEGORY_TREE WHERE PARENT_ID IN ( '000001', '000002', '000003')
				
			
			INSERT INTO MIG_CATEGORY (CATEGORY_ID, CAT_LEVEL)
			SELECT CAT.CATEGORY_ID, @CAT_LEVEL 
			FROM CATEGORY_TREE CAT 
					INNER JOIN #TMP_CATEGORY TMP 
					ON CAT.CATEGORY_ID = TMP.CATEGORY_ID

			DELETE FROM #TMP_CATEGORY
			INSERT INTO #TMP_CATEGORY
			SELECT CAT.CATEGORY_ID 
			FROM CATEGORY_TREE CAT 
				INNER JOIN MIG_CATEGORY MIG 
					ON CAT.PARENT_ID = MIG.CATEGORY_ID AND CAT_LEVEL = @CAT_LEVEL


		END
		ELSE
			BEGIN
				DELETE FROM #TMP_CATEGORY
			END
		
	END


END -- PROC END

