导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

fulianglove 2008-01-08 03:03:12

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



这样子写的话每一个商品种类对应一个结果集,怎么把它们放在同一个结果集中?无需关心需求是否合理,这是我为学习存储过程直接用旧的数据库的,谢谢啦
...全文
50 点赞 收藏 4
写回复
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
fulianglove 2008-01-09
多谢提醒,要不然想不起来
回复
-狙击手- 2008-01-08
select @number as 商品数量 ,@all_name as 商品名称, pk.productKindId as 种类ID ,pk.productKindName as 种类名称 from ProductKind pk where productKindId = @Id


---
用中间表呀
insert into ... select ...
回复
kk19840210 2008-01-08
把每个种类的结果集 报错到一个表里面
然后对该表操作
回复
-狙击手- 2008-01-08
用中间表呀
insert into ... select ...
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告