Show hierarchical data using a recursive CTE

In this article We are discussing show hierarchical data using CTE. With recursive query you can categorize your data in multi-levels. Here I am going to discuss an example how to show data in a hierarchical manner.
Table name is Categories Schema is

CREATE TABLE [dbo].[Categories](
    [CategoryId] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](max) NOT NULL,
    [Slug] [nvarchar](max) NULL,
    [Parent] [int] NULL,
    [Description] [nvarchar](max) NULL,
    [CreatedOn] [datetime] NULL,
    [UpdatedOn] [datetime] NULL,
    [IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
    [CategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Category table have two column CategoryId & Parent. Each category is associated with its parent & Root Parent is either null or 0 . I want to show full path of category Name like Root>Electronics>Computer

SQL Code for getting hierarchical data

ALTER PROCEDURE [dbo].[sp_GetCategoryGrid]
    -- Add the parameters for the stored procedure here
    @CategoryName NVARCHAR(MAX) = '' ,
    @Slug NVARCHAR(MAX) = '' 
AS 
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON ;
 
        SELECT  @CategoryName = ISNULL(@CategoryName, '')
        SELECT  @Slug = ISNULL(@Slug, '')
            
        ;WITH    n ( CategoryID, CategoryName, Slug, PARENT, Description, CreatedOn, UpdatedOn, Level, hierarchy, IsDeleted )
                  AS ( SELECT   CategoryID ,
                                CategoryName ,
                                Slug ,
                                PARENT ,
                                Description ,
                                CreatedOn ,
                                UpdatedOn ,
                                0 ,
                                CAST(CategoryID AS NVARCHAR) AS GENERATION ,
                                IsDeleted
                       FROM     [dbo].[Categories]
                       WHERE    ( PARENT IS NULL
                                  OR ( Parent = 0 )
                                )
                            
                       UNION ALL
                       SELECT   nplus1.CategoryID ,
                                ( n.CategoryName + ' > ' + nplus1.CategoryName ) AS CategoryName ,
                                nplus1.Slug ,
                                nplus1.PARENT ,
                                nplus1.Description ,
                                nplus1.CreatedOn ,
                                nplus1.UpdatedOn ,
                                Level + 1 ,
                                CAST(n.hierarchy + '.'
                                + CAST(nplus1.CategoryID AS NVARCHAR) AS NVARCHAR) ,
                                nplus1.IsDeleted
                       FROM     [dbo].[Categories] AS nplus1
                                INNER JOIN n ON nplus1.PARENT = n.CategoryID                       
                     )
            SELECT  CategoryID ,
                    CategoryName ,
                    Slug ,
                    Description ,
                    CreatedOn ,
                    UpdatedOn ,
                    Level ,
                    hierarchy
            FROM    n
            WHERE   ( ( Slug LIKE '%' + CAST(@Slug AS NVARCHAR(MAX)) + '%' )
                      OR ( @Slug = '' )
                    )
                    AND ( ( CategoryName LIKE '%'
                            + CAST(@CategoryName AS NVARCHAR(MAX)) + '%' )
                          OR ( @CategoryName = '' )
                        )
                    AND IsDeleted = 0
            ORDER BY hierarchy DESC 
    END

Pass Category Name & Slug(any character like >,<,|,/ etc...) & run stored procedure to get hierarchical data.