SQL存储过程

在这里做个备份

/*AutoInitProc
*
* Create for autoInit
* by vigor 2005.2.14
*
*/
USE chargeDB

/********创建视图******************************************************************/
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'StudentView_clas' AND type = 'V')
   DROP VIEW StudentView_clas
GO
CREATE  VIEW StudentView_clas
AS
  SELECT stud_id,stud_class,stud_clasflag FROM studentInfo
/**************************************************************************************/
GO

/********创建过程******************************************************************/
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoInitProc' AND type = 'P')
   DROP PROCEDURE AutoInitProc
GO
CREATE PROCEDURE AutoInitProc
  @operatorId char(3),  --传递进来的操作员代号
  @total int OUTPUT,    --传递出去的进度总数(用于进度显示)
  @current int OUTPUT   --传递出去的当前进度(用于进度显示)
AS
BEGIN --过程开始
  DECLARE @tari_id char(20),@tari_feeid char(8),@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   --开始事务
 
  SELECT COUNT(*)  FROM Tariff1Info GROUP BY Tari_classid 
  SET @total=@@ROWCOUNT        --返回总进度(即Tariff1Info中的班级个数)
  SET @current=0;

  DECLARE Tari_Cur SCROLL CURSOR       --声明外部游标
  FOR
  SELECT tari_classid FROM Tariff1Info ORDER BY tari_classid;

  OPEN Tari_Cur;                       --打开外部游标
  FETCH NEXT FROM Tari_Cur INTO  @tari_id;
  SELECT @STEPCOUNTER=COUNT(Tari_classid) FROM Tariff1Info WHERE Tari_classid=@tari_id;--统计当前(@tari_id)的数量,减少筛选次数

  WHILE @@FETCH_STATUS=0
  BEGIN
    DECLARE Stud_Cur SCROLL CURSOR    --声明内部游标
    FOR
    SELECT * FROM StudentView_clas WHERE stud_class=@tari_Id;
   
    OPEN Stud_Cur;                     --打开内部游标
    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  --声明零时游标
        FOR
        SELECT * FROM Tariff1Info WHERE tari_classid=@tari_Id;
       
        OPEN Temp_Cur;                  --打开零时游标
        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 '此项已经初始';
          ELSE
            INSERT TempPayable VALUES(@stud_id,@tari_feeid,@tari_feename,@tari_payable,GetDate(),@operatorId);
          FETCH NEXT FROM Temp_Cur INTO @tari_id,@tari_feeid,@tari_feename,@tari_payable;
        END;
        CLOSE Temp_Cur;                 --关闭零时游标
        DEALLOCATE Temp_Cur;            --释放零时游标
       END
       ELSE
         PRINT '此学生不参加班级自动初始';
      FETCH NEXT FROM Stud_Cur INTO  @stud_id, @stud_class, @stud_clsflag;
    END
    CLOSE Stud_Cur;                   --关闭内部游标
    DEALLOCATE Stud_Cur;              --释放内部游标
    FETCH RELATIVE @STEPCOUNTER FROM Tari_Cur INTO  @tari_id;--向后取@STEPCOUNTER的记录
    SET @current=@current+1;
    SELECT @STEPCOUNTER=COUNT(Tari_classid) FROM Tariff1Info WHERE Tari_classid=@tari_id;--统计当前(@tari_id)的数量,减少筛选次数
  END;
  CLOSE Tari_Cur;                     --关闭外部游标
  DEALLOCATE Tari_Cur;                --释放外部游标

  COMMIT TRANSACTION;   --提交事务

END;--过程结束

---测试------
GO

DECLARE @TOTAL INT,@CURRENT INT;
EXEC AutoInitProc 028,@TOTAL OUTPUT,@CURRENT OUTPUT
PRINT @TOTAL
PRINT @CURRENT