[T-SQL] Recursive CTE

Function implemented with normal recursive logic but have limit for 32 levels

CREATE FUNCTION [dbo].[OLD_RETURN_ROOTID](@CHILD_ID INT)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @PARENT_ID bigINT
SET @PARENT_ID = NULL
SET @PARENT_ID=
(
SELECT PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP
WHERE ID=@CHILD_ID AND SYSTEM_ID=2
)
IF (ISNULL(@PARENT_ID,0)=0)
SET @PARENT_ID=@CHILD_ID
ELSE
SET @PARENT_ID=(SELECT DBO.RETURN_ROOTID(@PARENT_ID))
RETURN(@PARENT_ID)
END

Function implemented with recursive CTE logic - no limits


CREATE FUNCTION [dbo].[RETURN_ROOTID](@CHILD_ID INT)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @PARENT_ID INT;
SET @PARENT_ID = NULL;
WITH RECURSIVECTE AS (
SELECT ID,PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP WHERE ID=@CHILD_ID
UNION ALL
SELECT CO.ID,CO.PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP AS CO
INNER JOIN RECURSIVECTE AS CTE
ON(CO.PARENTPRODUCTGROUP_ID =CTE.ID)
)
SELECT @PARENT_ID=Min(COALESCE(PARENTPRODUCTGROUP_ID,ID)) FROM RECURSIVECTE;
IF (@PARENT_ID=@CHILD_ID)
SET @PARENT_ID=@CHILD_ID
ELSE
SET @PARENT_ID=(SELECT DBO.RETURN_ROOTID(@PARENT_ID))
RETURN(@PARENT_ID)