USE [Test]
GO
/****** Object: StoredProcedure [dbo].[UXP_WS_SendShortMsg_20170406] Script Date: 04/07/2017 17:04:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
sql发送手机短信接口
ex:
declare @re int
execute dbo.UXP_WS_SendShortMsg '1307','验证码为:5892344',@re output
select @re
*/
CREATE proc [dbo].UXP_WS_SendShortMsg_20170406,
@msg nvarchar(4000),
@return int output
)
as
declare @return_desc varchar(100)
DECLARE @obj INT
DECLARE @url nVARCHAR(4000)
DECLARE @url_aliyun nVARCHAR(4000)--阿里云短信接口
DECLARE @response VARCHAR(5000)
declare @flowuserid varchar(30)
declare @uid nvarchar(30)
declare @pwd nvarchar(30)
set @uid='temul'
set @pwd='termupsd'
----阿里云 http://.88:9800/WS.asmx/SendMessage?TelCode=15919424&messageCenter={"carleadinfo":"aaa","usecarempinfo":"bbb"}&TemplateCode=110
--print @chno
set @url ='http://.88:9800/NewWS.asmx/SendMessage?'+@msg
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.6.0',@obj out
print @obj
EXEC sp_OAMethod @obj,'Open',null,'get',@url,false
EXEC sp_OAMethod @obj,'send'
EXEC sp_OAGetProperty @obj,'responseText',@response out
--SELECT @response [response]
EXEC sp_OADestroy @obj
declare @i int,@j int
declare @sourceC varchar(20),@placeC Varchar(30)
while charindex('非法字符是',@response,1)<>0
begin
set @i=charindex('是：',@response,1)
set @i=@i+1
set @sourcec=rtrim(substring(@response,@i+1,len(@response)-@i-9))
set @placec=left(@sourcec,1) + ' ' + substring(@sourcec,2,len(@sourcec)-1)
set @msg=replace(@msg,@sourcec,@placec)
--将非法字符间中间强行插入空格符,再次发送
set @url ='http://4.88:9800/NewWS.asmx/SendMessage?'+@msg
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.6.0',@obj out
EXEC sp_OAMethod @obj,'Open',null,'GET',@url,false
EXEC sp_OAMethod @obj,'send'
EXEC sp_OAGetProperty @obj,'responseText',@response out
EXEC sp_OADestroy @obj
end
IF CHARINDEX('success',@response,1)<>0 --SCM返回成功信息
begin
set @return=1
SET @return_desc='Success'
end
ELSE --
BEGIN
set @return=0
print @response
SET @return_desc='出错1,' + @response
end
print @return_desc
RETURN
GO
