[sql-server] Calling an API from SQL Server stored procedure

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?

I'd recommend using a CLR user defined function, if you already know how to program in C#, then the code would be;

using System.Data.SqlTypes;
using System.Net;

public partial class UserDefinedFunctions
{
 [Microsoft.SqlServer.Server.SqlFunction]
 public static SqlString http(SqlString url)
 {
  var wc = new WebClient();
  var html = wc.DownloadString(url.Value);
  return new SqlString (html);
 }
}

And here's installation instructions; https://blog.dotnetframework.org/2019/09/17/make-a-http-request-from-sqlserver-using-a-clr-udf/

The SQL Query select * from openjson ... works only with SQL version 2016 and higher. Need the SQL compatibility mode 130.

I think it would be easier using this CLR Stored procedure SQL-APIConsumer:

 exec [dbo].[APICaller_POST]
     @URL = 'http://localhost:5000/api/auth/login'
    ,@BodyJson = '{"Username":"gdiaz","Password":"password"}'

It has multiple procedures that allows you calling API that required a parameters and even passing multiples headers and tokens authentications.

Screams in to the void - just "no" don't do it. This is a dumb idea.

Integrating with external data sources is what SSIS is for, or write a dot net application/service which queries the box and makes the API calls.

Writing CLR code to enable a SQL process to call web-services is the sort of thing that can bring a SQL box to its knees if done badly - imagine putting the the CLR function in a view somewhere - later someone else comes along not knowing what you've donem and joins on that view with a million row table - suddenly your SQL box is making a million individual webapi calls.

The whole idea is insane.

This doing sort of thing is the reason that enterprise DBAs dont' trust developers.

CLR is the kind of great power, which brings great responsibility, and the above is an abuse of it.

Simple SQL triggered API call without building a code project

I know this is far from perfect or architectural purity, but I had a customer with a short term, critical need to integrate with a third party product via an immature API (no wsdl) I basically needed to call the API when a database event occurred. I was given basic call info - URL, method, data elements and Token, but no wsdl or other start to import into a code project. All recommendations and solutions seemed start with that import.

I used the ARC (Advanced Rest Client) Chrome extension and JaSON to test the interaction with the Service from a browser and refine the call. That gave me the tested, raw call structure and response and let me play with the API quickly. From there, I started trying to generate the wsdl or xsd from the json using online conversions but decided that was going to take too long to get working, so I found cURL (clouds part, music plays). cURL allowed me to send the API calls to a local manager from anywhere. I then broke a few more design rules and built a trigger that queued the DB events and a SQL stored procedure and scheduled task to pass the parameters to cURL and make the calls. I initially had the trigger calling XP_CMDShell (I know, booo) but didn't like the transactional implications or security issues, so switched to the Stored Procedure method.

In the end, DB insert matching the API call case triggers write to Queue table with parameters for API call Stored procedure run every 5 seconds runs Cursor to pull each Queue table entry, send the XP_CMDShell call to the bat file with parameters Bat file contains Curl call with parameters inserted sending output to logs. Works well.

Again, not perfect, but for a tight timeline, and a system used short term, and that can be closely monitored to react to connectivity and unforeseen issues, it worked.

Hope that helps someone struggling with limited API info get a solution going quickly.

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?)