SQL recursive query

Create Table [ Ptable ] (
[ ID ] [ Int ] Null ,
[ PID ] [ Int ] Null ,
[ Name ] [ Nchar ] ( 10 )
)
Go
Insert Into Ptable Values ( 1 , 0 , ' A ' )
Insert Into Ptable Values ( 2 , 0 , ' B ' )
Insert Into Ptable Values ( 3 , 1 , ' C ' )
Insert Into Ptable Values ( 4 , 1 , ' D ' )
Insert Into Ptable Values ( 5 , 2 , ' E ' )
Insert Into Ptable Values ( 6 , 3 , ' F ' )
Insert Into Ptable Values ( 7 , 3 , ' G ' )
Insert Into Ptable Values ( 8 , 4 , ' H ' )
Go

-- Query all the subnodes of Node 1.
With TMP As ( Select * From Ptable Where ID = 1
Union All Select Ptable. * From TMP, ptable Where TMP. ID = Ptable. PID
)
Select * From TMP

-- Query all the parent nodes of the eight nodes.
With TMP As (Select * From Ptable Where ID = 8
Union All Select Ptable. * From TMP, ptable Where TMP. PID = Ptable. ID
)
Select * From TMP;

-- Recursively Delete the statement of 1 node and all child nodes:
With TMP As ( Select * From PtableWhere ID = 1
Union All Select Ptable. * From TMP, ptable Where TMP. ID = Ptable. PID
)
Delete From Ptable Where Exists ( Select ID From TMP Where TMP. ID = Ptable. ID)