-- To get All parent categories and provided category in comma seperated string
-- DROP FUNCTION MIG_GET_PARENT_CATEGORIES_Fn
-- SELECT MIG_GET_PARENT_CATEGORIES_Fn('000010')
CREATE FUNCTION MIG_GET_PARENT_CATEGORIES_Fn(@CATEGORY_ID AS VARCHAR(16))
RETURNS VARCHAR(MAX)

BEGIN

DECLARE @Parent VARCHAR(16), @CATEGORIES_LIST VARCHAR(MAX) 
--SET @CATEGORY_ID = '000010'

SELECT @Parent=PARENT_ID FROM CATEGORY_TREE WHERE CATEGORY_ID=@CATEGORY_ID AND PARENT_ID <> ''

Declare @tempTbl TABLE
(
PARENT_ID varchar(16)
)
	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 AND PARENT_ID <> ''

	WHILE (@Parent <> '')
	BEGIN
		insert INTO @tempTbl
		SELECT PARENT_ID
		FROM CATEGORY_TREE
		WHERE CATEGORY_ID = @Parent AND PARENT_ID <> ''

		SELECT @Parent= PARENT_ID FROM CATEGORY_TREE WHERE CATEGORY_ID = @Parent


	END

--SELECT * FROM @tempTbl

select @CATEGORIES_LIST =
STUFF((	SELECT ',' + PARENT_ID
		FROM @tempTbl 
		FOR XML PATH (''))
		, 1, 1, '')  

RETURN @CATEGORIES_LIST

END
