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

[fulianglove]

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

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

[-狙击手-]

用中间表呀
insert into ... select ...

[kk19840210]

把每个种类的结果集 报错到一个表里面
然后对该表操作

[-狙击手-]

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


---
用中间表呀
insert into ... select ...

[fulianglove]

多谢提醒，要不然想不起来