存储过程中遍历一个表

 

CREATE PROCEDURE  InsertIntoTableReport

@lsd varchar(80)

AS

 

declare  @id int

declare  @txnum varchar(80)

declare  @name varchar(80)

declare  @price decimal(18,2)

declare  @shuliang int

declare  @tradeTime datetime

declare @userName  varchar(80)

 

set @tradeTime=getdate()

 

declare @i int

 

--遍历销售清单游标

declare getwplist cursor

for select  id,txnum,name,price,shuliang,tradeTime,admin

from vw_shopping

--where datediff(day,tradeTime,getdate())=0 and style='销售' and isCheck='是' and flag=0  and cate=4

where datediff(day,tradeTime,'2011-03-18')=0 and style='销售'  and flag=0  and cate=4

order by tradeTime desc

open getwplist

fetch NEXT   FROM  getwplist 

into @id,@txnum,@name,@price,@shuliang,@tradeTime,@userName

WHILE   @@FETCH_STATUS   =   0 

BEGIN 

    if not exists (select cid from report where cid=@id and lsd=@lsd )

    begin

set @i = 0

   while @i<@shuliang

begin 

insert into report(cid,txnum,name,price,shuliang,tradeTime,admin,lsd) values(@id,@txnum,@name,@price,1,@tradeTime,@userName,@lsd)

set @i = @i + 1

                          end

    end

    fetch NEXT   FROM  getwplist 

   into @id,@txnum,@name,@price,@shuliang,@tradeTime,@userName

end

close getwplist

DEALLOCATE getwplist

GO