CREATE PROC SyncFlagOperate_Proc
AS
BEGIN
-- 声明表变量,数据存在内存中、没有索引、没有索机制、不会生成SQL日志。适用于较小数据量
DECLARE @A TABLE(ID INT,ActionType INT,Operator VARCHAR(50),OperateDate DATETIME,
ItemId INT,FlagId VARCHAR(50),Unit VARCHAR(5),LabId INT,ItemName VARCHAR(500))
INSERT INTO @A
SELECT B1.ID
,B1.ActionType
,B1.Operator
,B1.OperateDate
,B3.ItemId
,B2.FlagId
,B3.Unit
,B1.LabId
,B3.ItemName
FROM [NCLab_ClientOperateLog] AS B1
LEFT JOIN [NCLab_RfidFlag] AS B2
ON B1.FlagId = B2.FlagId
LEFT JOIN [NCLab_Item] AS B3
ON B2.ItemId = B3.ItemId
WHERE SignStatus = 0 AND ActionType = 10 AND B2.FlagId IS NOT NULL
DECLARE cursor1 CURSOR Static -- 游标遍历
FOR SELECT * FROM @A
OPEN cursor1 -- 打开游标
DECLARE @ID INT,@ActionType INT,@Operator VARCHAR(50),@OperateDate DATETIME,
@ItemId INT,@FlagId VARCHAR(50),@Unit VARCHAR(5),@LabId INT,@ItemName VARCHAR(500)
FETCH NEXT FROM cursor1 INTO
@ID,@ActionType,@Operator,@OperateDate,
@ItemId,@FlagId,@Unit,@LabId,@ItemName --从游标变量中读取值
WHILE (@@FETCH_STATUS=0) --判断FETCH语句是否执行成功
BEGIN
select @LabId,@FlagId,@ItemName,@Operator,@OperateDate,@ID,@OperateDate
FETCH NEXT FROM cursor1 INTO
@ID,@ActionType,@Operator,@OperateDate,
@ItemId,@FlagId,@Unit,@LabId,@ItemName--读取游标变量中的数据
END
END
EXEC SyncFlagOperate_Proc