sql server 利用存储过程http请求调用URL链接访问方法

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

开启权限配置

DECLARE    @return_value int,
        @status int

EXEC    @return_value = [dbo].[P_HttpRequestData]
        @URL = N'https://test.GoldApi.com/Api/GetGoldSales',
        @status = @status OUTPUT

SELECT    @status as N'@status'

调用存储

USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_GetGoldSales]
AS
set nocount on
set transaction isolation level read uncommitted
set xact_abort on

exec [P_HttpRequestData] 'https://test.GoldApi.com/Api/GetGoldSales'

存储调用存储

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_HttpRequestData]

(
    @URL varchar(500),
    @status int=0 OUT,
    @returnText nvarchar(2000)='' OUT
)
AS
BEGIN
    DECLARE @object int,--声明变量
    @errSrc int   --声明变量
    /*初始化*/
    EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT;
    IF @status <> 0
    BEGIN
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT
     RETURN
    END
    /*创建链接*/
    EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL
    IF @status <> 0
    BEGIN
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT
     RETURN
    END
    EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
    /*发起请求信息*/
    EXEC @status= SP_OAMethod @object,'send',NULL
    IF @status <> 0
    BEGIN
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT
     RETURN
    END
  /*获取返回*/  
    EXEC @status= SP_OAGetProperty @object,'responseText',@returnText OUT 
   IF @status <> 0   
   BEGIN    
    EXEC SP_OAGetErrorInfo @object, @errSrc OUT   
   RETURN 
    END

END;