회원 잔액차감

DECLARE @del_date DATETIME
DECLARE @del_month INT
DECLARE @member_id VARCHAR(64)
DECLARE @cash INT
DECLARE @msg VARCHAR(64)

SET @del_month = -13

SET @del_date = DATEADD(MONTH, @del_month, GETDATE())

DECLARE pc_cursor INSENSITIVE CURSOR
FOR

 SELECT member_id, cash FROM subscriber WITH(NOLOCK)

  WHERE member_id = s.member_id
  AND cash > 0 AND visit_date  < @del_date

 

FOR READ ONLY

OPEN pc_cursor

 

FETCH NEXT FROM pc_cursor
INTO @member_id, @cash

 

WHILE @@FETCH_STATUS = 0
BEGIN

 

 SET @msg = '약관적용 건수차감  : ' +  CONVERT(VARCHAR, @cash)

 

 INSERT INTO memo(member_id, admin_id, memo) VALUES(@member_id, 'SYSTEM', @msg)
 UPDATE subscriber SET cash = 0 WHERE member_id = @member_id

 

 FETCH NEXT FROM pc_cursor
 INTO @member_id, @remain_cash
END

CLOSE pc_cursor
DEALLOCATE pc_cursor