Declare @SysNo int,@SysDetailNo int, @Detail varchar(50),@Detail1 varchar(50),@Detai2 varchar(50)
Declare Cur Cursor for Select SysNo,SysDetailNo from Depart
Open Cur
Fetch Cur Into @SysNo,@SysDetailNo
WHILE @@FETCH_STATUS=0
begin
Select @Detail=Detail,@Detail1=Detail1,@Detai2=Detail2 from grp where SysNo=@SysNo and SysDetailNO=@SysDetailNo
Exec dbo.Staff 'grp', @Detail ,@Detail1,@Detai2
Fetch Cur Into @SysNo,@SysDetailNo
end
Close Cur
Deallocate Cur
想问一下关于类似的语句,如何不使用游标来循环
Exec dbo.Staff 'grp', @Detail ,@Detail1,@Detai2如果每条都要用的话,没什么办法,最多也就是循环,也一样的和游标一下,解决办法,修改存储过程
Exec dbo.Staff 'grp', @Detail ,@Detail1,@Detai2
Fetch Cur Into @SysNo,@SysDetailNo
declare @sql varchar(max)
set @sql=''
select @sql=@sql+' exec dbo.Staff @Detail='''+Detail+''',@Detail1='''+Detail1+''',@Detai2='''+Detail2+''' '
from grp where SysNo=@SysNo and SysDetailNO=@SysDetailNo
exec(@sql)
另外再问一下, 关于在触发器内使用游标循环读出 ,有没有其它比较有效率的方法来代替呢?
例如:DECLARE cur_UADJSTOCK CURSOR Local FOR SELECT SysAdjNo, aDate, ComAdjNo, Stock, Depart, Staff, Editor FROM Inserted
OPEN cur_UADJSTOCK
FETCH cur_UADJSTOCK INTO @SysAdjNo, @aDate, @ComAdjNo, @Stock, @Depart, @Staff, @Editor
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE ADJSTOCK_SAME SET EditDate=GetDate(), aDate=@aDate, ComAdjNo=@ComAdjNo, Stock=@Stock, Depart=@Depart, Staff=@Staff, Editor=@Editor WHERE SysAdjNo=@SysAdjNo
FETCH cur_UADJSTOCK INTO @SysAdjNo, @aDate, @ComAdjNo, @Stock, @Depart, @Staff, @Editor
END
CLOSE cur_UADJSTOCK
DEALLOCATE cur_UADJSTOCK
-- #7 的游标应该是这个意思吧
UPDATE t1
SET t1.EditDate=GetDate(), t1.aDate=t2.aDate, t1.ComAdjNo=t2.ComAdjNo,
t1.Stock=t2.Stock, t1.Depart=t2.Depart, t1.Staff=t2.Staff, t1.Editor=t2.Editor
FROM ADJSTOCK_SAME t1, INSERTED t2
WHERE t1.SysAdjNo=t2.SysAdjNo