This post demonstrates the integration of external Rest APIs with Azure SQL Database and Azure API Management Service. By utilizing the system-stored procedure sp_invoke_external_rest_endpoint, you can make REST calls to endpoints such as Azure Functions in just a few lines of code. The sp_invoke_external_rest_endpoint works with various services including Azure Functions, Apps Service, App Service Environment, Static Web Apps, Container Instances, Logic Apps, Event Grids, Event Hubs, PowerApps, Cognitive Services, Power BI, Microsoft Graph, IoT Central, Analysis Services, and API management. If you wish to invoke an external REST API service not mentioned above, you can use the API management service to securely expose the desired service for use with sp_invoke_external_rest_endpoint. This post details how to make API calls from Azure SQL Database to a dummy JSON API by adding the REST endpoints to your Azure API Management Service.

To make REST API calls to external endpoints of dummy JSON, let's create API resources and add endpoints. Then API Management Service acts as a gateway for the external API calls and provides features such as caching and throttling.

Azure API Management Service Configuration:

When you log into your Azure portal, go to API Management Service, and create a new resource, if you don't have one already. When you open the resource, you will see the APIs tab on the left. When you click on APIs within the APIs tab, you will open a screen on the right where you can create and configure APIs as well as add operations to those APIs. To define a new API, click on the Add API tab and select http on the newly generated screen.

A modal will open as shown below. Keep the type Basic and then provide Display Name and Name. Also, input the Web Service URL, we are using dummyjson for demonstration so input the base URL path of the same which is https://dummyjson.com . After that click on create button.

A new screen will be presented, allowing you to add and configure REST endpoint operations, as well as a Frontend Card for OpenAPI specification editor, Inbound and outbound processing to modify requests or responses, and finally, a Backend Card providing a form-based editor as well as a code-based editor.

By clicking on Add Operation, the Frontend form-based editor will open. Enter the desired details like Display Name and Name. In the URL input field, choose the HTTP method as GET to fetch all product data. Then, enter the endpoint path in the accompanying field, in our case, we added "/products". You may also provide descriptions and relevant tags. Below Description, there are several tabs such as Template, Query, Headers, Request, and Responses that allow you to specify necessary URL template parameters, additional query parameters, important request headers, request body information and define response status codes, content-types and schemas respectively. Finally, save your changes by clicking on the save button.

Then, select the operation we just created and click on the Test tab above to get the HTTP Request URL and Ocp-Apim Subscription Key. You can find them by scrolling down in the HTTP Request Section, inside the code snippet, and clicking on the eye icon.

Click the Send button below to test out the API call and see the response.

We have to connect Azure SQL Database, to call the API from T-SQL code. Below is the code for both the GET method and the POST method. You can add the POST endpoint operation to API Management Service with all the required fields and test it out.

Making External API endpoint call with Azure SQL Database

declare @ret as int, @response as nvarchar(max), @headers nvarchar(4000);
set @headers = N'{"Ocp-Apim-Subscription-Key": "<YourOcpApimSubscriptionKey>"}';

exec @ret = sp_invoke_external_rest_endpoint 
    @method = 'GET',
    @url = 'https://api-management-service-kiran04.azure-api.net/products',
    @headers = @headers,
    @response = @response output,
    @timeout=30;
select @ret as ReturnCode, @response as Response;

```
Below is the example for POST method for reference. You can try adding the POST endpoint operation in API Management Service with all the needed fields and test it out. After that you can try out the POST operation with below example code.
```
declare @ret int, @response nvarchar(max), @payload nvarchar(max), @headers nvarchar(4000)
set @payload = N'{
  "title": "Large Paint Brush",
  "description": "An Large Paint Brush for painting on walls or large surface.",
  "price": 900,
  "discountPercentage": 1.96,
  "rating": 4.69,
  "stock": 94,
  "brand": "ABCXYZ",
  "category": "home appliances"
}'
set @headers = N'{"Ocp-Apim-Subscription-Key": "<YourOcpApimSubscriptionKey>"}';

exec @ret = sp_invoke_external_rest_endpoint
    @method = 'POST',
    @url = 'https://api-management-service-kiran04.azure-api.net/products/add',
    @headers = @headers,
    @payload = @payload,
    @timeout = 30,
    @response = @response output;

select @ret as ReturnCode, @response as Response;

API Management Service is required since we were using external API endpoints for demonstration. As mentioned earlier, this is not required for using other Azure services.