[MS-SQL]CURSOR 사용시 진행 상황을 표시하자

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[_Object]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [_Object]
GO
 
CREATE TABLE [dbo]._Object
(
  ID  int,
  Context varchar(128),
  Date  datetime
)
 
declare @ID     int
declare @Context  varchar(128)
 
SET @ID = 0
 
WHILE( @ID < 10000 )
BEGIN
  SET @ID = @ID + 1
  SET @Context = 'Context' + convert( varchar(10), @ID )
 
  INSERT INTO [dbo].[_Object] (ID, Context, Date) VALUES ( @ID, @Context, GETDATE() )
END
 
SELECT * FROM _Object

DECLARE cursor_Obj CURSOR FAST_FORWARD
FOR SELECT ID, Context FROM _Object
 
DECLARE @ID  int
DECLARE @Context varchar(128)
OPEN cursor_Obj
 
FETCH NEXT FROM cursor_Obj INTO @ID, @Context
WHILE (@@fetch_status = 0)
BEGIN
  UPDATE _Object SET Context = 'Context' + convert(varchar(10), 10001 - @ID)
  WHERE ID = @ID
 
  FETCH NEXT FROM cursor_Obj INTO @ID, @Context
END
 
CLOSE cursor_Obj
DEALLOCATE cursor_Obj
GO

SET NOCOUNT ON
 
---------------------------- 진행 상황 표시 관련 변수 선언 ----------------------------
DECLARE @ProcessRowCount  INT
DECLARE @ProcessCurCount  INT
DECLARE @ProcessPercentage  INT
DECLARE @ProcessPrevPercentage  INT
DECLARE @StartTime    DATETIME
DECLARE @ProcessRatio  INT
 
SET @ProcessRowCount = 0
SET @ProcessCurCount = 0
SET @ProcessPercentage = 0
SET @ProcessPrevPercentage = 0
SET @StartTime = GETDATE()
 
SET @ProcessRatio    = 10 -- Ratio 값 범위(2 - 100) : 값 1 미지원
 
SELECT  @ProcessRowCount = count('') FROM _Object WITH(NOLOCK)
---------------------------------------------------------------------------------------
 
DECLARE cursor_Obj CURSOR FAST_FORWARD
FOR SELECT ID, Context FROM _Object WITH(NOLOCK)
 
DECLARE @ID  int
DECLARE @Context varchar(128)
 
OPEN cursor_Obj
 
FETCH NEXT FROM cursor_Obj INTO @ID, @Context
WHILE (@@fetch_status = 0)
BEGIN
  ---------------------------- 진행 상황 표시 관련 변수 선언 ----------------------------
  SET @ProcessCurCount = @ProcessCurCount + 1
  SET @ProcessPercentage = (@ProcessCurCount * 100 / @ProcessRowCount)
 
  IF ( (@ProcessPercentage % @ProcessRatio) = 0 )
  BEGIN
    IF ( (@ProcessPrevPercentage % @ProcessRatio) <> (@ProcessPercentage % @ProcessRatio) )
    BEGIN
      PRINT 'Elapsed Time : ' + convert( varchar(128), GETDATE() - @StartTime, 114 )
      + ',    Processing...' + convert( varchar(4), @ProcessPercentage ) + '%'
    END
  END
 
  SET @ProcessPrevPercentage = @ProcessPercentage
  ---------------------------------------------------------------------------------------
 
  UPDATE _Object SET Context = 'Context' + convert(varchar(10), 10001 - @ID)
  WHERE ID = @ID
 
  FETCH NEXT FROM cursor_Obj INTO @ID, @Context
END
 
CLOSE cursor_Obj
DEALLOCATE cursor_Obj
GO