直接SQL报表执行存储过程问题

在MS SQL数据库中新增一个存储过程，在制作直接SQL报表的时候，提示“SQL测试不通过”，但是在MS SQL客户端直接exec 存储过程是可以查询的

PS：存储过程脚本：
create proc tmpBOMDetails
as
begin

declare @sql nvarchar(4000)
set @sql = 'with Tmp_BOMDetail as
(
Select a.FMATERIALID as ParentMaterial,a.FNUMBER,b.FMATERIALID as SubMaterial ,
CONVERT(decimal(23,10),1) as ParentFenzi,CONVERT(decimal(23,10),1) as ParentFenMu,
b.FBASENUMERATOR as SubFenZi,b.FBASEDENOMINATOR as SubFenMu
from T_ENG_BOM a
inner join T_ENG_BOMCHILD b on a.FID = b.FID
where a.FMATERIALID = 109569
union all
Select c.SubMaterial as ParentMaterial,d.FNUMBER,e.FMATERIALID as SubMaterial,
c.SubFenZi as ParentFenZi,c.SubFenMu as ParentFenMu,
e.FBASENUMERATOR as SubFenZi,e.FBASEDENOMINATOR as SubFenMu
from Tmp_BOMDetail c
inner join T_ENG_BOM d on c.SubMaterial = d.FMATERIALID
INNER JOIN T_ENG_BOMCHILD e on d.FID = e.FID
)
Select a.*,b.FNUMBER,c.FNAME from Tmp_BOMDetail a inner join T_BD_MATERIAL b on a.SubMaterial = b.FMATERIALID
inner join T_BD_MATERIAL_L c on b.FMATERIALID = c.FMATERIALID
where a.SubMaterial not in (Select Distinct ParentMaterial from Tmp_BOMDetail)'
exec sp_executesql @sql
end

在直接SQL报表里面的“数据源”直接输入 exec tmpBOMDetails

各位大神帮忙看下怎么回事