SqlServer2008R2调用WebApi接口

1.开启SqlServer通讯配置

------------------- 开启Sql Server 通讯配置 -------------------
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

 

2.创建函数

 
 DROP FUNCTION FN_HTTP_POST;
CREATE FUNCTION FN_HTTP_POST(@URL VARCHAR(256),
@DATA VARCHAR(8000),
@REQ_H_ACCEPT VARCHAR(256),
@REQ_H_CONTENT_TYPE VARCHAR(256)
)
RETURNS VARCHAR(5000)
AS 
BEGIN

DECLARE 
@object int,
@returnStatus int,
@returnText varchar(5000),
@errMsg varchar(2000),
@httpStatus varchar(20)
;

/* 初始化 */  
    EXEC @returnStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT;  
    
    IF @returnStatus <> 0  
    BEGIN  
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('初始化对象失败，' + @errMsg + ISNULL(@returnText,''));  
    END   
/*创建链接*/
EXEC @returnStatus= SP_OAMethod @object,'open',NULL,'post',@URL,'false';

IF @returnStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('创建连接失败，' + @errMsg + ISNULL(@returnText, ''));
END

EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT;
EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE;
EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000';

/*发起请求*/
EXEC @returnStatus= SP_OAMethod @object,'send',NULL,@DATA;
IF @returnStatus <> 0 
BEGIN 
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('发起请求失败，' + @errMsg + ISNULL(@returnText, ''));
END

/*获取HTTP状态代码*/
EXEC @returnStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT;

    IF @returnStatus <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @returnText OUT;
        RETURN ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, ''));
    END

    IF @httpStatus <> 200
    BEGIN
        RETURN ('访问错误，HTTP状态代码:' + @httpStatus);
    END
/*获取返回信息*/
EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT;

IF @returnStatus <> 0 
BEGIN 
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('获取返回信息失败，' + @errMsg + ISNULL(@returnText, ''));
END

 
RETURN @returnText;
END 
;

 

3..执行调用

SELECT dbo.FN_HTTP_POST('http://localhost:28775/api/User/LoginUser',
'{"userName":"dzw159","password":"123456","type":1}',
'application/json','application/json');

 

4.结果

补充（HttpGet）：

 

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE FUNCTION [dbo].[FN_HTTP_GET](
@ServiceUrl VARCHAR(8000)
)
RETURNS VARCHAR(5000)
AS 
BEGIN

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 --异常输出
RETURN @ResponseText 
--Exec sp_OADestroy @Object

END
;
GO

调用：

SELECT dbo.FN_HTTP_GET('http://localhost:1006/api/EAS/GetTravelApplicationStatus?Number=' + ISNULL('8c1d6178-eab8-3237-589a-3c9d5dd436c3',''));

结果：

 

 

 

 

感谢：https://blog.csdn.net/zhou279818998/article/details/99448510?spm=1035.2023.3001.6557&utm_medium=distribute.pc_relevant_bbs_down.none-task-blog-2~all~sobaiduend~default-2.nonecase&depth_1-utm_source=distribute.pc_relevant_bbs_down.none-task-blog-2~all~sobaiduend~default-2.nonecase

 

 

这边我也参考了：http://sql.zhizuobiao.com/sql-18083100079/

https://www.cnblogs.com/lflyq/p/6065160.html

https://blog.csdn.net/xieweikun_7/article/details/117363665

https://www.cnblogs.com/zhaoxxnbsp/p/13570873.html

 https://www.cnblogs.com/dong-1984dd/archive/2012/01/17/SQL-Web.html

（但是这个好像要SqlServer2012）

 注：这里【 https://www.cnblogs.com/dong-1984dd/archive/2012/01/17/SQL-Web.html】有说是需要安装Soap，用上面函数调用的方式不需要安装，

但是如果用2012的我就不知道了，下载地址：https://www.fxxz.com/soft/118617.html