SQLServer中使用存储过程调用WebAPI

SQLServer中使用存储过程调用WebAPI

下面是封装的调用web API的存储过程，其中sp_OACreate和sp_OAMethod以及sp_OADestroy是系统自带扩展存储过程，具体使用文档可以参考官网,均是对OLE对象的操作

---

ALTER 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