SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE proc_get_mt
AS
begin
declare cur_mt cursor for
Select * from OpenQuery(wxtl_test,
'select send_id,
mobile,
send_msg,
sys_id
from wxtlplatform.send
where SEND_STATUS = 0')
open cur_mt
declare @v_send_id varchar(20),
@v_mobile varchar(21),
@v_send_msg varchar(500),
@v_message varchar(200),
@v_sys_id varchar(20),
@v_cmd varchar(500),
@v_ext_cmd nvarchar(500),
@v_ext_code varchar(20),
@v_OrgAddr varchar(100)
fetch next from cur_mt into @v_send_id,@v_mobile,@v_send_msg,@v_sys_id
while @@fetch_status = 0
begin
PRINT ' '
SELECT @v_message = '----- Send info : ' + @v_send_id + ' ' + @v_mobile + ' ' + @v_send_msg
PRINT @v_message
--查找扩展码
select @v_ext_cmd = 'Select @v_ext_code=down_lt from OpenQuery(wxtl_test,''select down_lt from wxtlplatform.sys_info where sys_id = ' +
@v_sys_id + ''')'
exec sp_executesql @v_ext_cmd, N'@v_ext_code varchar(20) output', @v_ext_code output
Select @v_ext_code
PRINT @v_ext_code
PRINT @v_ext_cmd
select @v_OrgAddr = '62090001001' + @v_ext_code
--exec proc_get_mt
--插入MAS MT表
INSERT INTO tbl_SMSendTask
(CreatorID,
TaskName,
SmSendedNum,
OperationType,
SuboperationType,
SendType,
OrgAddr,
DestAddr,
SM_Content,
SendTime,
NeedStateReport,
ServiceID,
FeeType,
FeeCode,
MsgID,
SMType,
MessageID,
DestAddrType,
SubTime,
TaskStatus,
SendLevel,
SendState,
TryTimes,
Count,
SuccessID,
Reserve1,
Reserve2)
VALUES(
'0000',
'',
0,
'WAS',
'66',
1,
@v_OrgAddr,
@v_mobile,
@v_send_msg,
GETDATE(),
1,
'EIE',
'01',
'0',
'',
0,
'0',
0,
GETDATE(),
0,
0,
3,
0,
0,
0,
'',
'')
--'0404','',0,'WAS','66',1,'620900010010404','13800138000',convert(varchar(100),getdate(),121),getdate(),
--1,'EIE','01','0','',0,'0',0,getdate(),0,0,0,0,0,0)
--修改华夏基金MT表
select @v_cmd = 'update OpenQuery(wxtl_test, ''select SEND_STATUS from wxtlplatform.send where send_id = ' +
@v_send_id + ''') set SEND_STATUS=2'
PRINT @v_cmd
--exec(@v_cmd)
--游标移动
fetch next from cur_mt into @v_send_id,@v_mobile,@v_send_msg,@v_sys_id
end
close cur_mt
deallocate cur_mt
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
posted on 2008-02-28 16:27
一凡 阅读(305)
评论(0) 编辑 收藏 所属分类:
DATABASE