這樣?

use Tempdb
go
--> --> 
 
if not object_id(N'A') is null
    drop table A
Go
Create table A([ID] int,[PID] nvarchar(4))
Insert A
select 1,N'1001' union all
select 2,N'1002' union all
select 3,N'1003' union all
select 4,N'0007'
Go

--> --> 
 
if not object_id(N'B') is null
    drop table B
Go
Create table B([ID] int,[ATOID] int,[PID] nvarchar(4))
Insert B
select 1,1,N'0003' union all
select 2,1,N'0004' union all
select 3,1,N'0005' union all
select 4,1,N'0007' union all
select 5,2,N'0008' union all
select 6,2,N'0009' union all
select 7,3,N'0007' union all
select 8,3,N'0009' union all
select 9,4,N'0010' union all
select 10,4,N'0011'
Go
CREATE PROCEDURE pA(@ATOID int)
AS
;WITH C
AS
(
Select * from B WHERE [ATOID]=@ATOID
UNION ALL
SELECT b.* FROM C AS a INNER JOIN B ON a.[ID]=b.[ATOID] INNER JOIN A AS c ON a.PID=c.[PID]
)
SELECT * FROM C  AS a WHERE NOT EXISTS(SELECT 1 FROM C WHERE [ATOID]=a.ID)
go

EXEC pA 1
/*
ID    ATOID    PID
2    1    0004
3    1    0005
9    4    0010
10    4    0011
*/