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

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

　　

转载于:https://www.cnblogs.com/yhyjy/archive/2013/03/20/2970642.html