Error 1 is occurred because you didn't specify alias name for sum(p.qty) in the first
Error 2, you didn't put ';' at the end of the first CTE.
Error 3, I am not able to tell the reason because you didn' provide any info of it.
Error 4, in the last statement, replace 'Join' with ','.
Above all syntactical errors. One more thing, CTE result can be persisted for its immediate select query only. So you need to convert your query like this..
alter proc [dbo].[SpCateProducts](@cate_id int)
, tbl2 as
select top 1 pname from product join subcategory
select top 1 subcate_id
from subcategory Join tbl1 ON
select * from tbl2
Please provide inputs and output of your require so that we can give our best solutions.