随笔-204  评论-90  文章-8  trackbacks-0
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 一凡 阅读(299) 评论(0)  编辑  收藏 所属分类: DATABASE

只有注册用户登录后才能发表评论。


网站导航: