SQL Server调用Webservice

测试目标：

• 在SQL Server中成功调用Webservice

---

测试内容：

1. 调用Webservice无参例子
2. 调用Webservice有参例子

---

测试过程：

1. 无参传递的Webservice：方法名：HelloWorld

declare @ServiceUrl as varchar(1000) 
declare @UrlAddress varchar(500)
set @UrlAddress = 'http://localhost:8066/WebServiceHome.asmx/'

declare @FunName varchar(50)
--WebService中调用的方法名：例如'getMobileCodeInfo'
set @FunName = 'HelloWorld'   

set @ServiceUrl = @UrlAddress + @FunName             
               
Declare @Object as Int
Declare @ResponseText as Varchar(8000)
                  
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT     
Select @ResponseText     
Exec sp_OADestroy @Object
GO

2. 有参传递的Webservice：方法名：SeeSee 入参：input

declare @ServiceUrl as varchar(1000) 
declare @UrlAddress varchar(500)
set @UrlAddress = 'http://localhost:8066/WebServiceHome.asmx/'

declare @FunName varchar(50)
--WebService中调用的方法名：例如'getMobileCodeInfo'
set @FunName = 'SeeSee'   

declare @P1 varchar(800)
set @P1 = 'input'

declare @P1_Value varchar(100)
set @P1_Value = 'sql'
--拼接地址
set @ServiceUrl = @UrlAddress + @FunName + '?' + @P1 + '=' + @P1_Value                 
               
Declare @Object as Int
Declare @ResponseText as Varchar(8000)
                  
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT     
Select @ResponseText     
Exec sp_OADestroy @Object
GO

测试产出：

参考博客：https://blog.csdn.net/xiaoqiaoluanwu/article/details/79160843

• CSDN 技术博客 1 篇