N level category display in tree structure using stored procedure
CREATE PROCEDURE ASD_SP_GetCategoryId
(
@CultureId INT = 1 ,
@CategoryID INT = NULL
)
AS
WITH CTE ( CategoryID, CategoryName, ParentCategoryID, level, ParentName, IsActive )
AS ( SELECT CategoryID ,
CategoryName ,
ParentCategoryID ,
0 ,
CASE WHEN ParentCategoryID = 0
THEN CAST(CategoryName AS VARCHAR(MAX))
ELSE CAST(CategoryName AS VARCHAR(MAX))
END ,
IsActive
FROM Asd_vw_CategoryMAster
WHERE ParentCategoryID = 0
AND CategoryID <> ISNULL(@CategoryID, -1)
AND IsDeleted = 0
AND cultureId = @CultureId
UNION ALL
SELECT C.CategoryID ,
C.CategoryName ,
C.ParentCategoryID ,
level + 1 ,
CAST (CT.ParentName + ' >> ' + C.CategoryName AS VARCHAR(MAX)) ,
C.IsActive
FROM Asd_vw_CategoryMAster C
INNER JOIN CTE CT ON CT.CategoryID = C.ParentCategoryID
WHERE c.cultureId = @CultureId
AND C.IsDeleted = 0
AND C.CategoryID <> ISNULL(@CategoryID, -1)
)
SELECT CategoryID ,
ParentName AS CategoryName ,
ParentCategoryID ,
IsActive
FROM CTE
ORDER BY ParentName
