从存储过程调用Web Api 2端点

some legacy SQL stored procedure (SQL Server 2008) needs to get some information from a web service (Web Api 2) that I provided.

某些遗留SQL存储过程(SQL Server 2008)需要从我提供的Web服务(Web Api 2)获取一些信息。

How to call the endpoint (in GET), retrieve the data (JSON format) and translate the data received in a temporary table to be used in the rest of the sproc?

如何调用端点(在GET中),检索数据(JSON格式)并转换临时表中接收的数据以在sproc的其余部分中使用?

Thanks

2 个解决方案

#1

The best way would be to create Used-defined CRL function and call your Web API from there, so you can use full power of C# and .Net libraries to do web calls and parse Json. There is plenty of information on the internet about that. For example: https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/. This is not about WebAPI in particular, but you can get the idea from there.

最好的方法是创建Used-defined CRL函数并从那里调用Web API,这样你就可以使用C#和.Net库的全部功能来进行Web调用并解析Json。互联网上有大量关于此的信息。例如:https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/。这不是特别关注WebAPI,但您可以从那里获得想法。

Please note that it would require deployment of your custom assembly with CLR Function(s) to the SQL Server.

请注意,它需要将具有CLR功能的自定义程序集部署到SQL Server。

Edit:

There is a way to do it in TSQL using OLE Automation. See an example here, but it is is much harder, less documented and you will probably spend time inventing you own bicycle instead of using ready solutions from with CLR functions.

有一种方法可以使用OLE自动化在TSQL中执行此操作。在这里查看一个示例,但它更难,记录更少,您可能会花时间发明自己的自行车,而不是使用CLR功能的现成解决方案。

#2

You can do it easy without need Writing CRL :

无需编写CRL即可轻松完成:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object

#1