一、首先要开启组件的配置

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ole Automation Procedures';

GO

二、调用webservice

1、使用sqlserver调用对应的接口以及结果

declare @ServiceUrl as varchar(1000) 

set @ServiceUrl = 'http://localhost:19930/LoginWebService.asmx/Login'

DECLARE @data varchar(max);

set @data='userid=1&phone=17647582259'                  

Declare @Object as Int

Declare @ResponseText AS  varchar(1000)   ;      

Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;

Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'

Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'

Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据

Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

EXEC sp_OAGetErrorInfo @Object --异常输出

Select  @ResponseText 

Exec sp_OADestroy @Object

GO

三、调用webapi

1、接口调用以及结果

GET操作

declare @ServiceUrl as varchar(1000) 

set @ServiceUrl = 'http://xxxxx.com/api?userid=6'

DECLARE @data varchar(max);

set @data=''                  

Declare @Object as Int

Declare @ResponseText AS  varchar(8000)   ;      

Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;

Exec sp_OAMethod @Object, 'open', NULL, 'GET',@ServiceUrl,'false'

Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据

Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

EXEC sp_OAGetErrorInfo @Object --异常输出

Select  @ResponseText 

Exec sp_OADestroy @Object

GO

POST操作

declare @ServiceUrl as varchar(1000) 

set @ServiceUrl = 'http://xxx.com/webapi'

DECLARE @data varchar(max);

--发送数据

set @data='CityName=SubmitSystemName=%E7%99%BE%E5%BA%A6%E5%8F%8D%E9%A6%88&OriginID=2d90660c-436c-4e12-bfa6-e849a06b2c51&Price=10000&IsAccurate=False&PriceType=1&UserKeyId=a669e4ec7bdc47a7b6c2c334ebe1a50c&signature=X8p3lIZT0Ba3LeiC6irm3%2FMnlE8%3D&time=1452735047291'                   

Declare @Object as Int

Declare @ResponseText AS  varchar(8000)   ;      

Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;

Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'

Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'

Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据

Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

EXEC sp_OAGetErrorInfo @Object --异常输出

Select  @ResponseText 

Exec sp_OADestroy @Object

GO

四、存储过程

CREATE PROC P_Service 

@ServiceUrl varchar(1000),

@data varchar(max),

@GetOrPost varchar(100),

@HeadersKey varchar(200),

@HeadersValue varchar(200)

as

Declare @Object as Int

Declare @ResponseText AS  varchar(1000)   ;      

Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;

Exec sp_OAMethod @Object, 'open', NULL, @GetOrPost,@ServiceUrl,'false'

Exec sp_OAMethod @Object, 'setRequestHeader', NULL, @HeadersKey,@HeadersValue

Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据

Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

--EXEC sp_OAGetErrorInfo @Object --异常输出

Select  @ResponseText 

Exec sp_OADestroy @Object

GO