참고 : 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