SQLSERVER调用WebAPI和WebService

参考原文：https://blog.csdn.net/qq243348167/article/details/124243810

第一步需开启组件

开启组件
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

 其中sp_OACreate和sp_OAMethod以及sp_OADestroy是系统自带扩展存储过程，对OLE对象的操作

1.通过http协议post调用webapi接口（json数据格式）

declare @ServiceUrl as varchar(1000) 
set @ServiceUrl = 'http://172.16.170.192:803/api/wardRound/GetInfo'
DECLARE @data varchar(max);
--发送数据
set @data='{"ID": 67,"UserID": 10}'                   

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/json;charset=UTF-8'
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

调用结果

 

 2.调用WebService

DECLARE @sUrl NVARCHAR(100)=N'http://127.0.0.1/WebService/Dye.asmx?op=ColorSystem'
 
DECLARE @sSoapContent NVARCHAR(2000)=N''
 
SELECT @sSoapContent=N'<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <ColorSystem xmlns="http://tempuri.org/">
      <iCompanyId>1</iCompanyId>
      <sColorNo>2</sColorNo>
    </ColorSystem>
  </soap12:Body>
</soap12:Envelope>'
 
DECLARE @sResult NVARCHAR(MAX)
SELECT @sResult=dbo.fnpbWebServiceInvoke(@sUrl,N'',N'',@sSoapContent).value(N'(/Result[1])','NVARCHAR(MAX)')
SELECT @sResult=REPLACE(@sResult,N'"',N'')
SELECT @sResult=REPLACE(@sResult,N'{',N'')
SELECT @sResult=REPLACE(@sResult,N'}',N'')
SELECT @sResult=REPLACE(@sResult,N'[',N'')
SELECT @sResult=REPLACE(@sResult,N']',N'')

封装成存储的方式方便调用

Create PROCEDURE [dbo].[Proc_CallWebApi]
    -- Add the parameters for the stored procedure here
    @ApiUrl VARCHAR(200), --api请求地址
    @RequestType VARCHAR(5),--请求类型:POST,GET
    @ResponseText NVARCHAR(4000) OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @data varchar(8000);
    --发送数据
    set @data=''

    Declare @Object as Int
    Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT
    Exec sp_OAMethod @Object, 'open', NULL, @RequestType,@ApiUrl,'false'
    IF @RequestType='POST'
    BEGIN
        Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
    END
    Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    --EXEC sp_OAGetErrorInfo @Object --异常输出
    --Select  @ResponseText as ResponseText
    --Select * from [dbo].[ParseJSON](@ResponseText)
    Exec sp_OADestroy @Object

END