Hi,
Error 1 is occurred because you didn't specify alias name for sum(p.qty) in the first
CTE.
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)
as
begin
with
tbl1 as
(
select sb.subcate_name,
sum(p.qty) Qty
.............
)
, tbl2 as
(
select top 1 pname from product join subcategory
on product.subcate_id=
(
select top 1 subcate_id
from subcategory Join tbl1 ON
<your condition="">
)
)
select * from tbl2
</your>
Please provide inputs and output of your require so that we can give our best solutions.
Thank you.