Be a backup here
/ * AutoinitProc ** Create for Autoinit * by Vigor 2005.2.14 ** / Use Chargedb
/ ******** Create a view *********************************************************** ************************************ / if EXISTS (Select Name from sysobjects where name = 'studentview_clas' and type = 'v') DROP VIEW STUDENTVIEW_CLASGOCREATE VIEW StudentView_Clas As SELECT Stud_ID, Stud_class, Stud_Clasflag from StudentInfo / ******************************************************** ********************************************************* / Go
/ ******** Creation process *********************************************************** ************************************ / IF EXISTS (Select Name from sysobjects where name = 'autoinitproc' and type = 'p') Drop Procedure AutoinitPrococreate Procedure AutoinitProc @operatorid char (3), - Passing the operator code @total int output, - Total number of progress (for progress display) @Current Int Output - Current progress (for Progress display) ASBEGIN - process start declare @tari_id char (20), @ Tari_feename char (20), @ Tari_Payable Char (4); declare @stud_id char (15), @ stud_class char (20 ), @ stud_clsflag char (1); declare @stepcounter int; set nocount on begin transaction - Start transaction SELECT Count (*) from tariff1info group by tari_classid set @total = @@ rowcount - Return to total progress (ie Tariff1Info Class number) set @ current = 0;
Declare Tari_Cur Scroll Cursor - Declaration External Cursor for SELECT TARI_CLASSID from Tariff1Info Order by Tari_ClassID;
Open TARI_CUR; - Open External Cursor Fetch next from tari_cur @tari_id; select @ stepcounter = count (tari_classid) from tariff1info where tari_classid = @ TARI_ID; - Count the number of current (@tari_id), reduce filtering
WHILE @@ FETCH_STATUS = 0 BEGIN DECLARE Stud_Cur SCROLL CURSOR - declared inside a cursor FOR SELECT * FROM StudentView_clas WHERE stud_class = @ tari_Id; OPEN Stud_Cur; - open internal cursor FETCH NEXT FROM Stud_Cur INTO @stud_id, @stud_class, @stud_clsflag; WHILE @@ FETCH_STATUS = 0 BEGIN IF @ stud_clsflag = 1 BEGIN dECLARE Temp_Cur SCROLL cURSOR - declare zero cursor FOR SELECT * FROM Tariff1Info WHERE tari_classid = @ tari_Id; oPEN Temp_Cur; - open the zero cursor FETCH NEXT FROM Temp_Cur INTO @tari_id, @ tari_feeid, @ tari_feename, @ tari_payable; WHILE @@ FETCH_STATUS = 0 BEGIN IF EXISTS (SELECT * FROM TempPayable WHERE tepa_studid = @ stud_id AND tepa_feeid = @ tari_feeid) PRINT 'this has an initial'; ELSE INSERT TempPayable VALUES (@stud_id, @ TARI_FEEID, @ Tari_feename, @ Tari_Payable, getdate (), @ Operatorid); Fetch next from temp_cur @ tari_id, @ tari_feeid, @ tari_feename, @ tari_payable; end ; CLOSE Temp_Cur; - close to zero cursor DEALLOCATE Temp_Cur; - releasing zero cursor END ELSE PRINT 'students do not participate in this initial class automatically'; FETCH NEXT FROM Stud_Cur INTO @stud_id, @stud_class, @stud_clsflag; END CLOSE Stud_Cur; - - turn off the internal cursor DEALLOCATE Stud_Cur; - release the internal cursor fETCH RELATIVE @STEPCOUNTER FROM Tari_Cur INTO @tari_id; - taking back @STEPCOUNTER recording SET @ current = @ current 1; SELECT @ STEPCOUNTER = COUNT (Tari_classid) FROM Tariff1Info WHERE TARI_CLASSID = @ TARI_ID; - Statistics Current (@
Tari_id), reduce the number of filters End; Close Tari_Cur; - Close the external cursor deallocate tari_cur; - Release the external cursor commit transport; - Submit a transaction
END; - end of the process
--- Test ------ Go
Declare @total int, @ current int; exec autoinitproc 028, @ Total Output, @ Current OutputPrint @totalprint @current
