sqlserver使用存储过程发送http请求

本文主要向大家介绍了SQLServer数据库访问发送Http请求，通过具体的内容向大家展现，希望对大家学习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

 

/*

参数说明?

@URL=http请求地址

@status=状态代?

@returnText=返回?

@object=对象令牌

@errSrc=错误源编?

*/

CREATE PROCEDURE P_GET_HttpRequestData(

@URL varchar(500),

@status int=0 OUT,

@returnText varchar(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, @returnText OUT

 RETURN

END

 

/*创建链接*/

EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL

IF @status <> 0

BEGIN

 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText 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, @returnText OUT

 RETURN

END

     

/*获取返回*/

EXEC @status= SP_OAGetProperty @object,'responseText',@returnText OUT

IF @status <> 0 

BEGIN 

 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT

 RETURN

END

END;

 

 

----测试

CREATE PROCEDURE IPCC_WEBSERVICE_PACKID(@parametersStr VARCHAR(255),@outText VARCHAR(255) OUT)

AS

BEGIN

  DECLARE @returnText VARCHAR(500)

  DECLARE @status int

  DECLARE @urlStr VARCHAR(255)

  SET @urlStr = 'http://172.17.5.14:9090/df/sendDataToIVR.action?parametersStr='+@parametersStr+'&ifaceType=4';

EXEC P_GET_HttpRequestData    @urlStr, @status OUTPUT,@returnText   OUTPUT;

   SET @outText = @returnText;

   print @outText ; ---打印

END;