ALTER PROCEDURE [dbo]. [Proc_updmonthstatus] As BEGIN declare @aint, @errorint, @Id varchar ( $), @Area varchar ( $), @Department varchar ( $), @Year int, @Month intSet@a=1 Set@error =0--declares that the cursor is UID declare order_cursor cursor for(SelectId,area,department,reportyear,reportmonth fromAreamonthreportwherestatus=3) --Open cursor--Open Order_cursor--Start loop cursor variable--FETCH Next fromorder_cursor into @Id, @Area, @Department, @Year, @Month while@ @FETCH_STATUS =0--Returns the state of the last cursor executed by the FETCH statement--begin---Add a day to transact records DECLARE @CreatorId varchar ( $) SET @CreatorId=(SelectTOP1Creatorid fromAuditoptionswhereAreamonthreportid=@Id ORDER BY createtime desc) insert INTO auditoptions (Id,link,createuser,creatorid,optiontext,createti Me,areamonthreportid) VALUES (NEWID (),'Headquarters Audit', @CreatorId,"','Automatic Consent', GETDATE (), @Id)--Modify Projectmonthtrack Update ProjectmonthtrackSetprojectstatus=5 where[Email protected] and [email protected] and [email protected] and reportmonth=@Month--Modify Areamonthreport Update AreamonthreportSetstatus=5 whereId=@IdSet@[email protected]+1 Set@error = @error + @ @ERROR--record whether the SQL is correct after each run, 0 fetch next correctly fromOrder_cursor into @Id, @Area, @Department, @Year, @Month--go to the next cursor, there will be no dead loop end Endgo
The above code mainly implements a add, and 2 modification records
SQL Server Cursors