Call a Web Api 2 endpoint from a stored procedure

Question

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

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?

Thanks

Answer 1

The best way would be to create Used-defined CLR 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.

Please note that it would require deployment of your custom assembly with CLR Function(s) to the 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.

Answer 2

You can do it easily without writing CLR :

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