--EXEC MIG_VALIDATE_DUPL_CATEGORY_ATTRIBUTES_Sp '', ''
CREATE OR ALTER PROCEDURE MIG_VALIDATE_DUPL_CATEGORY_ATTRIBUTES_Sp
(
	@STATUS INT = 0 OUT,
	@MESSAGE NVARCHAR(999) = NULL OUT
)
AS 
BEGIN
	DECLARE @CNT INT
	DECLARE @Parent VARCHAR(50)
	DECLARE @CATEGORY_ID NVARCHAR(32)
	DECLARE @ITR INT = 0
	DECLARE @TOT_CNT INT

	SELECT @TOT_CNT = MAX(ZZ_ID) FROM CATEGORY_TREE

	WHILE @ITR < @TOT_CNT
	BEGIN
		SELECT @CATEGORY_ID = CATEGORY_ID FROM CATEGORY_TREE WHERE ZZ_ID = @ITR

		SELECT @Parent=PARENT_ID 
		FROM CATEGORY_TREE 
		WHERE CATEGORY_ID=@CATEGORY_ID  
	
		SELECT CATEGORY_ID INTO #tempTbl FROM CATEGORY_TREE WHERE PARENT_ID IS NULL

		INSERT INTO #tempTbl  
		SELECT CATEGORY_ID     
		FROM CATEGORY_TREE     
		WHERE CATEGORY_ID = @CATEGORY_ID  

		INSERT INTO #tempTbl  
		SELECT PARENT_ID     
		FROM CATEGORY_TREE     
		WHERE CATEGORY_ID = @CATEGORY_ID  
		
		WHILE (@Parent <> '')  
		BEGIN  
			INSERT INTO #tempTbl  
			SELECT PARENT_ID     
			FROM CATEGORY_TREE     
			WHERE CATEGORY_ID = @Parent  
  
			SELECT @Parent= PARENT_ID FROM CATEGORY_TREE WHERE CATEGORY_ID = @Parent  
		END  

		SELECT @CNT = COUNT(*)
		FROM CATEGORY_ATTRS
		WHERE CATEGORY_ID IN (SELECT CATEGORY_ID FROM #tempTbl)
		GROUP BY ATTR_NAME
		HAVING COUNT(ATTR_NAME) > 1

		DROP TABLE #tempTbl
		IF @CNT > 0
		BEGIN
			BREAK;
		END
		SET @ITR = @ITR + 1
	END

	IF @CNT > 0
	BEGIN
		SET @STATUS = 1
		SET @MESSAGE = 'Duplicate Category Attribute Names are detected.'
	END
	ELSE
	BEGIN
		SET @STATUS = 0
		SET @MESSAGE = ''
	END
	
	SELECT @STATUS, @MESSAGE
END

