通过存储过程直接访问服务器URL

最新推荐文章于 2024-07-28 03:18:03 发布

原创最新推荐文章于 2024-07-28 03:18:03 发布 · 242 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Go

SQLSERVER技术专栏收录该内容

31 篇文章

订阅专栏

本文介绍了一个SQL Server中的存储过程，用于发送SMS通知。该过程检查smsSend表中的记录，针对成功或失败的消息构建通知，并使用HTTP请求将消息发送到指定接收者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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