Sql server HTTP POST 请求接口函数

       场景是对方发布了一个http的post接口，需要从Sql server数据直接访问这个接口地址，请求接口返回的数据。请求参数是json格式的。oracle数据库楼主做过，但是sqlserver数据库没做过，上网搜了一些资料，然后在生产库测试了下，有数据返回，但没对返回的数据进行解析，有空在分享解析接口返回数据的脚本。本文先分享请求接口的函数。另外，可以尝试将函数写成存储过程。本文也把猜想的存储过程脚本分享出来，但没做具体的调试，不知是否成功，仅供参考，读者有意研究的话可以尝试自行调试，如果可以麻烦，跟帖分享出来。谢谢！

函数脚本如下

-- HTTP POST 请求
-- [测试]
-- SELECT dbo.FN_HTTP_POST('http://ip:port/xxx/xxxx/barinfo','{"data":{"barcode":"01xxxxx03"}}','application/json','application/json; charset=UTF-8');

CREATE FUNCTION FN_HTTP_POST(
@URL VARCHAR(256),
@DATA VARCHAR(4000),
@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;

存储过程脚本（未经过验证，仅供参考，不保证能用）

CREATE PROCEDURE [dbo].[usp_Util_HttpPost]
    @Url NVARCHAR(4000),
    @PostData NVARCHAR(4000)
AS
BEGIN        
    SET NOCOUNT ON
    DECLARE @ErrMsg VARCHAR(5000)
    DECLARE @token INT,@result INT,@returnTextErr VARCHAR(200),@HttpStatus VARCHAR(200)         

    EXECUTE @result = sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @token OUT
    IF @result <> 0
    BEGIN
        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
        SET @ErrMsg = '初始化对象失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
        RAISERROR(@ErrMsg,16,-1)
    END

   EXECUTE @result = sp_OAMethod @token, 'open', NULL, 'POST', @Url,'false'
   IF @result <> 0
    BEGIN
        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
        SET @ErrMsg = '创建连接失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
        RAISERROR(@ErrMsg,16,-1)
    END
  
   EXECUTE @result = sp_OAMethod @token, 'setRequestHeader', NULL,'Content-Type', 'application/json; charset=UTF-8' 
   IF @result <> 0
        BEGIN
            EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
            SET @ErrMsg = '设置RequestHeader属性失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
            RAISERROR(@ErrMsg,16,-1)
        END    

    EXECUTE @result = sp_OAMethod @token,'send', NULL, @PostData--发送请求
    IF @result <> 0
    BEGIN
        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
        SET @ErrMsg = '发送请求头失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
        RAISERROR(@ErrMsg,16,-1)
    END

    EXECUTE @result = sys.sp_OAGetProperty @token, 'Status', @HttpStatus OUT
    IF @result <> 0
    BEGIN
        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
        SET @ErrMsg = '读取[Status]属性值失败，' + @ErrMsg + ISNULL(@returnTextErr,'')
        RAISERROR(@ErrMsg,16,-1)
    END

    IF @HttpStatus <> 200
    BEGIN
        SET @ErrMsg ='访问错误,http状态代码,'+@HttpStatus+''
        RAISERROR(@ErrMsg,16,1)
    END
            
    IF @result <> 0
    BEGIN
        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
        SET @ErrMsg = '获取回复报文失败：' + ISNULL(@ErrMsg, '') + '，'+ ISNULL(@returnTextErr, '')
        RAISERROR(@ErrMsg,16,-1)
    END

    EXECUTE @result = sp_OADestroy @token
    IF @result <> 0
    BEGIN
        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT
        SET @ErrMsg = '释放资源对象，' + @ErrMsg + ISNULL(@returnTextErr, '')
        RAISERROR(@ErrMsg,16,-1)
    END
END