Sql 使用游标

DECLARE data_cursor CURSOR

FOR

    WITH    T0

              AS ( SELECT   COUNT(f.DeptID) SubmitCount ,

                            f.DeptID

                   FROM     biz.FormCollect f

                            INNER JOIN biz.Results r ON f.IID = r.ObjectID

                            INNER JOIN biz.NumberLimit n ON n.DeptID = f.DeptID

                   WHERE    r.[Status] = 1

                   GROUP BY f.DeptID

                 ),

            T1

              AS ( SELECT   b.BatchID ,

                            c.BatchName ,

                            n.IID ,

                            n.DeptName ,

                            t0.SubmitCount

                   FROM     biz.FormCollect f

                            INNER JOIN biz.BatchRelationShips b ON f.IID = b.FormID

                            INNER JOIN T0 ON T0.DeptID = f.DeptID

                            INNER JOIN biz.NumberLimit n ON n.DeptID = t0.DeptID

                            INNER JOIN bas.ChooseBatch c ON c.IID = b.BatchID

                   WHERE    c.ModuleID = 2

                 )

    SELECT DISTINCT

            IID ,

            BatchID ,

            SubmitCount

    FROM    T1

Go

OPEN data_cursor

DECLARE @IID INT ;

DECLARE @BatchID INT ;

DECLARE @SubmitCount INT ;

WHILE @@FETCH_STATUS = 0

    BEGIN

        FETCH NEXT FROM data_cursor INTO @IID, @BatchID, @SubmitCount

        PRINT @IID;

        PRINT @BatchID;

        PRINT @SubmitCount;

        PRINT '';

        INSERT  INTO biz.NumberLimitCount

                ( NumberLimitID ,

                  BatchID ,

                  LimitCount ,

                  CurrentCount ,

                  ModifyTime

                )

        VALUES  ( @IID , -- NumberLimitID - int

                  @BatchID , -- BatchID - int

                  0 , -- LimitCount - int

                  @SubmitCount , -- CurrentCount - int

                  GETDATE()  -- ModifyTime - datetime

                )

    END

CLOSE data_cursor

DEALLOCATE data_cursor