达人帮我看看这个存储过程！！！

ALTER PROCEDURE [dbo].[NewTest]

	--@pkId varchar(2) --商品种类ID

	--@name varchar(45) output

	-- Add the parameters for the stored procedure here



AS



BEGIN

	declare @id varchar(2)

	declare @name varchar(45)

	declare @all_name varchar(45)

	declare @number int

	set @number = 0

	

	declare all_product cursor for

		select productKindId  from productKind

	open all_product

	fetch next from all_product into @id

	while @@fetch_status = 0 

	begin

		--连接商品名称的游标

		declare concat_name cursor 

			for select productName  from product p where p.productKind = @id --order by p.productCode desc

		open concat_name

		fetch  next from concat_name into @name

		while @@fetch_status = 0 

		begin 

			if @all_name is null

				set	@all_name = @name

			else

				set	@all_name = @all_name + @name

			set @name = null

			fetch  next from concat_name into @name

				if @name is not null	

					set	@all_name = @all_name + ',' 

				else 

					set	@all_name = @all_name

			set @number = @number + 1

		end

	close concat_name

	deallocate concat_name

	print @id

	select @number as 商品数量 ,@all_name as 商品名称, pk.productKindId as 种类ID  ,pk.productKindName as 种类名称 from ProductKind pk where productKindId = @Id

	set @number = 0

	set @all_name = null

	fetch next from all_product into @id

	end





	--select @number as 数量

	--select @all_name as 商品名称

close all_product

	deallocate all_product	

END

表结构：

product 商品表

userId varchar(4)   -- 主键

productCode varchar(8) -- 主键

productName varchar(45)

productKind varchar(2) --对应商品种类表主键



productKind 商品种类表

productkindId -- 主键

productkindName

这样子写的话每一个商品种类对应一个结果集，怎么把它们放在同一个结果集中？无需关心需求是否合理，这是我为学习存储过程直接用旧的数据库的，谢谢啦