Get all nested subcategories or data from a table in sql server

Consider the situation-
ID      Name                 PID
1       a                         0        
2       -a.a                     1
3       --a.a.a                2
4       --a.a.b               2
5       ---a.a.b.a           4
6       ---a.a.b.b           4
7       ----a.a.b.b.a       6
8       -a.b                   1
9       -a.c                   1
10      --a.c.a              9
11      --a.c.b              9
12      b                      0
13      -b.a                 12
14      -b.b                 12
15      --b.b.a             14

and you want to select all the subcategories to n level under any category i.e.
if you want that all subcategories under "b" will be selected -
Result-
12      b                       0
13      -b.a                  12
14      -b.b                  12
15      --b.b.a              14

or

All subcategories under category "a" -
result-
1       a                        0        
2       -a.a                    1
3       --a.a.a                2
4       --a.a.b               2
5       ---a.a.b.a           4
6       ---a.a.b.b           4
7       ----a.a.b.b.a      6
8       -a.b                   1
9       -a.c                   1
10      --a.c.a              9
11      --a.c.b              9

-- =============================================

CREATE PROCEDURE [dbo].[Sp_SubCategoryListings]

    -- Add the parameters for the stored procedure here

    @ID int

    AS

    BEGIN

    WITH childCatagories(oids,name, child_oids,RecursionLevel) AS

   (

    SELECT b.ID,b.Name, b.PID, 0 AS RecursionLevel

    FROM dbo.tbl_category AS b

   WHERE b.PID = @ID -- 'everything for the home' oid

   UNION ALL

   SELECT bom.ID, bom.Name,bom.PID, RecursionLevel + 1

   FROM dbo.tbl_category AS bom

   INNER JOIN childCatagories AS p

  ON bom.PID = p.oids

   )

  SELECT * FROM childCatagories AS p ORDER BY RecursionLevel

  OPTION (MAXRECURSION 1000);

  END