这段存储过程,每次只能insert一条记录,但我查询后符合条件的有好几条,如何做循环呢?请各位帮助,谢谢!
CREATE PROCEDURE path_insert
@flowname varchar(50),
@appdept varchar(50),
@reportname varchar(50)
AS
declare @ActionSid int,@appuser varchar(50),@num intselect @flowname=flowname,@ActionSid=ActionSid,@appuser=appuser,@num=num from flowstep
where flowname=@flowname and appdept= @appdept
insert into flowpath(flowname,ActionSid,appuser,num)
values (@flowname,@ActionSid,@appuser,@num)update flowpath set reportname =@reportname where reportname is null and flowname=@flowname
GO
CREATE PROCEDURE path_insert
@flowname varchar(50),
@appdept varchar(50),
@reportname varchar(50)
AS
declare @ActionSid int,@appuser varchar(50),@num intselect @flowname=flowname,@ActionSid=ActionSid,@appuser=appuser,@num=num from flowstep
where flowname=@flowname and appdept= @appdept
insert into flowpath(flowname,ActionSid,appuser,num)
values (@flowname,@ActionSid,@appuser,@num)update flowpath set reportname =@reportname where reportname is null and flowname=@flowname
GO
CREATE PROCEDURE path_insert
@flowname varchar(50),
@appdept varchar(50),
@reportname varchar(50)
AS
insert into flowpath(flowname,ActionSid,appuser,num)
select flowname,ActionSid,appuser,num from flowstep
where flowname=@flowname and appdept= @appdept update flowpath set reportname =@reportname where reportname is null and flowname=@flowname
GO
还有循环效率很低的
------------
具体内容没看.可以把需要的记录查询出来做为子查询.然后直接插入.
比如:insert into b(col1,col2) select col1,col2 from b where 条件
先fetch一组数据,以fetch状态作为循环条件,别忘了在循环结束时去fetch下组数据。呵呵。
示例如下
DECLARE select_cur CURSOR LOCAL SCROLL READ_ONLY FOR
select ItemID, ItemName, WhereSQL, CountContent, LevelValue
from T_ReportItem
where Activity = 'y' and LevelValue = 2
OPEN select_cur
FETCH NEXT FROM select_cur INTO @itemid, @itemName, @wheresql, @content, @levelvalue
WHILE( @@FETCH_STATUS = 0 )
BEGIN
print 'start'
if exists(select * from T_ReportData where StatDay=convert(nvarchar(10),@tempdate,121) and ItemID=@itemid)
begin
FETCH NEXT FROM select_cur INTO @itemid, @itemName, @wheresql, @content, @levelvalue
continue
end
set @tempdatestr = convert(varchar(10),@tempdate,121)
insert into T_ReportData(StatDay, ItemID, ItemName, Total, LevelValue) exec SP_ReportCalculate @wheresql, @tempdatestr, @itemid, @itemName, @levelvalue
FETCH NEXT FROM select_cur INTO @itemid, @itemName, @wheresql, @content, @levelvalue
END希望对LZ有所帮助
@flowname varchar(50),
@appdept varchar(50),
@reportname varchar(50)
AS
insert into flowpath(flowname,ActionSid,appuser,num)
select flowname,ActionSid,appuser,num from flowstep
where flowname=@flowname and appdept= @appdept update flowpath set reportname =@reportname where reportname is null and flowname=@flowname
GO