Calling an API from SQL Server stored procedure

Question

Calling an API from ASP.NET Web Form is very easy.

WebClient wc = new WebClient(); string urlData = wc.DownloadString("http://xxx.xxx.xx.xx/sssss/getResponse.do?ID=testing"); 

But can we call an API from SQL Server stored procedure.

If yes then how can we call an API from a SQL Server stored procedure and how can we get API response?

Answer 1

Please see a link for more details.

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 

Answer 2

I worked so much, I hope my effort might help you out.

Just paste this into your SSMS and press F5:

Declare @Object as Int; DECLARE @hr  int Declare @json as table(Json_Table nvarchar(max))  Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT; IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',                  'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)                  'false' IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object Exec @hr=sp_OAMethod @Object, 'send' IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText' -- select the JSON string select * from @json -- Parse the JSON string SELECT * FROM OPENJSON((select * from @json), N'$.elements') WITH (          [type] nvarchar(max) N'$.type'   ,       [id]   nvarchar(max) N'$.id',       [lat]   nvarchar(max) N'$.lat',       [lon]   nvarchar(max) N'$.lon',       [amenity]   nvarchar(max) N'$.tags.amenity',       [name]   nvarchar(max) N'$.tags.name'      ) EXEC sp_OADestroy @Object 

This query will give you 3 results:

1. Catch the error in case something goes wrong (don't panic, it will always show you an error above 4000 characters because NVARCHAR(MAX) can only store till 4000 characters)

2. Put the JSON into a string (which is what we want)

3. BONUS: parse the JSON and nicely store the data into a table (how cool is that?)