sql server 语句 循环 、游标、临时表

ts7758258

create TABLE #TMP (I_SarInfor varchar(10))  -- 创建一个临时表
insert into #TMP
SELECT A.I_SailorInfoID FROM      
CERT_SailorLetter A
LEFT JOIN
CREW_SailorInfo B
ON A.I_SailorInfoID = B.I_SailorInfoID
LEFT JOIN
COM_LetterName C
ON A.NVC_LetterNameCode = C.NVC_LetterNameCode
LEFT JOIN
COM_Position D
ON B.NVC_PositionCode = D.NVC_PositionCode
LEFT JOIN
COM_CodeC E
ON E.I_CodeMID = '606'
AND A.NVC_CountryCode = E.NVC_Code
LEFT JOIN
COM_CodeC F
ON F.I_CodeMID = '201'
AND B.NVC_SailorStateCode = F.NVC_Code
LEFT JOIN
COM_CodeC G
ON G.I_CodeMID = '308'
AND A.NVC_LocationCode = G.NVC_Code
LEFT JOIN
COM_Position H
ON A.NVC_SuitPositionCode = H.NVC_PositionCode
LEFT JOIN
FPD_Client I
ON A.I_ClientID = I.I_ClientID
LEFT JOIN
PFD_SailorHistory J
ON B.I_SailorInfoID = J.I_SailorInfoID
AND J.VC_IsEnd = '1'
LEFT JOIN COM_ScanFile
ON A.I_SailorLetterID = COM_ScanFile.I_SailorLetterID and COM_ScanFile.IMG_File is not null
WHERE
1 = 1
AND A.NVC_LocationCode = '00002'
AND B.NVC_SailorStateCode = '00001'
GROUP BY A.I_SailorInfoID                --以上是查询出来的数据放在临时表中
DECLARE @SQL VARCHAR(2000)               --定义变量  大家懂
DECLARE @I_SailorInfoID VARCHAR(2000)
DECLARE @BMZJE VARCHAR(2000)
declare BMcursor cursor for
select I_SarInfor  from #TMP
open BMcursor
fetch next from BMcursor into @I_SailorInfoID ---这样你就能循环取到每一行的I_SailorInforId值
while @@FETCH_STATUS=0
begin
SET @SQL='UPDATE CERT_SailorLetter set NVC_LocationCode=''00004'' WHERE NVC_LocationCode=''00002'' and I_SailorInfoID='+@I_SailorInfoID+''   --要循环的sql 语句
EXEC(@SQL)          --执行
fetch next from BMcursor into @I_SailorInfoID
end
close BMcursor
deallocate BMcursor