본문 바로가기

work/mssql

procedure 에서 loop 사용

참고 : http://www.sqlusa.com/bestpractices/whilelooptablevariable/


create procedure [PROC_SMS_SEND]

AS 

begin

set nocount on


DECLARE @cnt INT, @seq INT, @imax INT, @i INT

DECLARE @sender VARCHAR(15)

DECLARE @receiver VARCHAR(15)

DECLARE @message VARCHAR(80)

DECLARE @sms_queue TABLE(

rowid int identity (1, 1),

seq int,

phone varchar(15),

[message] varchar(80)

)


insert @sms_queue

select seq, phone, [message] from [mps].[SMS_QUEUE] order by seq


set @imax = @@ROWCOUNT 

set @i = 1

while (@i <= @imax)

begin

SELECT @sender = replace(PHONE_C, '-', '') FROM SYS_USER where IS_SUPER = 'Y'; 

select @seq = seq,

  @receiver = phone,

  @message = [message]

from @sms_queue

where rowid = @i;

insert into [SMS].MPS.mps.sms_queue (phone, message, is_send)

values(@receiver,@message,'N');

delete from [mps].[SMS_QUEUE] where SEQ = @seq;

set @i = @i + 1

end


end

go


** 프로시져 예약 작업으로 주기적으로 실행하기(첨부)


sql실행 bat파일

send_sms.bat


프로시저 실행 sql

send_sms.sql