?

?

CREATE???????? procedure usp_alertSmsSentMessage
as

declare @id int
declare @to varchar(20)
declare @msg varchar(8000)
declare @response varchar(8000)
declare @url varchar(8000)

while 1 = 1
begin
?set @id = null
?select top 1
??@id = ss.id,
??@to = ss.personId,
??@msg = '以下手机短信发送到:' + ss.phoneNumber + ', ' + ss.customerName + ' 成功![[br]][[/br]]' + ss.content + '[[br]][[/br]]' + convert(varchar(19), ss.registerDate,

120)
?from smsSend ss
?where handleFlag = 1 and sendResult > 0 and echoFlag > 0
?order by ss.id desc

??? if @id is null
??? begin
??? ?select top 1
??? ??@id = ss.id,
??? ??@to = ss.personId,
??? ??@msg = '以下手机短信发送到:' + ss.phoneNumber + ', ' + ss.customerName + ' 失败![[br]][[/br]]' + ss.content + '[[br]][[/br]]' + convert(varchar(19), ss.registerDate,

120)
??? ?from smsSend ss
??? ?where (handleFlag = 1 and sendResult < 0)
??? ?order by ss.id desc
??? end

?if @id is not null
?begin
??if @to is not null and @to<>'JCNEP0000'
??begin
???
?????set @url = 'http://127.0.0.1:8998/SendMessage?to=' + @to + '&msg=' + @msg + '&rand=' + convert(varchar(20), rand())
--主要通过usp_httpRequest来连接服务器，源码在附件中

?????exec usp_httpRequest
??????@url,
??????'GET',
??????'',
??????@response OUT
?????print @response
????
???
???
??end
??update smsSend set handleFlag = 10 where id = @id
??--insert into smsSend2(id,phoneNumber,content,sendDate,updateDate,sendResult,echoFlag,registerDate,personId,customerId,customerName,linkManId,clientIp,handleFlag)
??--select id,phoneNumber,content,sendDate,updateDate,sendResult,echoFlag,registerDate,personId,customerId,customerName,linkManId,clientIp,handleFlag from smsSend where

id = @id
??--delete from smsSend where id = @id
??continue
?end
?
?WAITFOR DELAY '00:00:17'
end

-- exec usp_alertSmsSentMessage

GO

1 楼 wqy159 2011-09-08