N level category display in tree structure using stored procedure

 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