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