Invoke REST API Endpoint from SQL Server 2025

Problem

One highly anticipated new feature in SQL Server 2025 is the ability to call an external REST API endpoint from the database server itself. This new feature opens the door to new data integration scenarios and delivers on the promise to “bring AI closer to data.” What are the steps to follow if you want to use this new feature?

Solution

To invoke an external REST API, we can use the new sp_invoke_external_rest_endpoint stored procedure available by default in SQL Server 2025 and Azure SQL. There are two prerequisites to being able to use this functionality:

  1. Set the global database configuration to enable using sp_invoke_external_rest_endpoint:
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
rest api enable sp_invoke_external_rest_endpoint
  1. Ensure the user who will be running the stored procedure has EXECUTE ANY EXTERNAL ENDPOINT database permission. If needed grant that permission explicitly:
GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [<PRINCIPAL>];

Scenario

Having applied the configurations, to follow this document hands on, you need to:

  1. Install SQL Server 2025. Follow this link to register for a free trial.
  2. Next, you will need some data to be represented as JSON so you can send it as payload to your own API. As an example, you can import the Adventure Works 2022 sample database to your newly installed SQL Server. To get the database backup file follow this link. For a step by step guide on how to import the backup check this article here. Finally, you can check this document on how to get data as a JSON object.

In my case I already have a small database table containing some weather data:

--MSSQLTips.com T-SQL
  SELECT [Timestamp]
         , RawData
    FROM dbo.WeatherData
ORDER BY [Timestamp] DESC
data scenario preview

RawData is a native JSON column. We will send values from it to the external API.

JSON Payload

Here is what the JSON object in the RawData column looks like:

json payload preview

We are interested in sending the JSON temperature value (in green) to our cloud-hosted API endpoint to parse out the temperature as a single floating-point number.

API Endpoint

To get the temperature value as a floating-point number, I have developed an API service using Logic Apps. The logic app service supports a trigger called “When a HTTP request is received” which creates a cloud-hosted API endpoint which I can call. Here is what my API looks like:

api preview

This is what the service does:

  1. Once saved, based on the HTTP trigger, the logic app flow creates an endpoint in Azure along with an embedded security token
  2. Initializes an empty variable to hold the result
  3. Loops over each element in the sensordatavalues JSON array
  4. Checks if the key value_type matched the target value “BME280_Temperature”
  5. In case it did, parses the value from the value key and sets the variable
  6. Finally, if the operations so far have succeeded, responds with a code 200 (solid line branch) or 400 (dashed line branch).

We will later see what the API responses look like from SQL Server.

Syntax

This is the general syntax for sp_invoke_external_rest_endpoint:

EXECUTE @returnValue = sp_invoke_external_rest_endpoint
  [ @url = ] N'url'
  [ , [ @payload = ] N'request_payload' ]
  [ , [ @headers = ] N'http_headers_as_json_array' ]
  [ , [ @method = ] 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE' | 'HEAD' ]
  [ , [ @timeout = ] seconds ]
  [ , [ @credential = ] credential ]
  [ , @response OUTPUT ]
  [ , [ @retry_count = ] # of retries if there are errors ]

Here is a breakdown of the arguments:

ArgumentData typeDefaultExplanation
@urlnvarchar(4000)NoneThe API endpoint we are calling.
@payloadnvarchar(max)NoneThe Unicode string contains the payload in JSON, XML, or text format.
@headersnvarchar(4000)NoneRequest headers if any, formatted as flat JSON.
@methodnvarchar(6)POSTCalling method. Supported values are GET, POST, PUT, PATCH, DELETE and HEAD.
@timeoutpositive smallint30Time in seconds allowed for the whole call to run.
@credentialSysnamenoneDatabase scoped credential to be injected as authentication information. Note: in a typical scenario you would use an OAUTH token in the @headers.
@responsenvarchar(max)noneThe response coming from the API.
@retry_counttinyint0How many times the stored procedure retries the call if there is an error.

Allowed endpoints

There is a list of allowed endpoints spanning every Azure service you can think of, including Logic Apps, Azure Functions and Azure OpenAI. The list applies to Azure SQL Database and Azure SQL Managed Instance only. You can call any endpoint from SQL Server 2025, including self-hosted ones if the call is over HTTPS. The list of supported endpoints is too long to quote in this document. You can find it here.

Demo

Here is a reusable script wrapping the call into a single stored procedure with error handling:

--MSSQLTips.com T-SQL
01: CREATE OR ALTER PROCEDURE dbo.usp_ParseTemperatureData
02:     @Id INT
03: AS
04: BEGIN
05:     SET NOCOUNT ON;
06: 
07:     BEGIN TRY
08:         DECLARE @payload NVARCHAR(MAX);
09:         DECLARE @retcode INT;
10:         DECLARE @response NVARCHAR(MAX);
11: 
12:         IF NOT EXISTS (SELECT 1 FROM WeatherData WHERE Id = @Id)
13:         BEGIN
14:             RAISERROR('No record found in WeatherData for Id = %d.', 16, 1, @Id);
15:             RETURN;
16:         END
17: 
18:         SELECT @payload = CAST(RawData AS NVARCHAR(MAX))
19:         FROM WeatherData
20:         WHERE Id = @Id;
21: 
22:         IF @payload IS NULL OR LTRIM(RTRIM(@payload)) = ''
23:         BEGIN
24:             RAISERROR('The RawData for Id = %d is null or empty.', 16, 1, @Id);
25:             RETURN;
26:         END
27: 
28:         EXECUTE
29:             @retcode = sp_invoke_external_rest_endpoint
30:             @url = 'https://prod-217.westeurope.logic.azure.com:443/workflows/7099e5390d364a01acc504a490faaf66/triggers/When_a_HTTP_request_is_received/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2FWhen_a_HTTP_request_is_received%2Frun&sv=1.0&sig=sas_token',
31:             @payload = @payload,
32:             @method = 'POST',
33:             @credential = NULL,
34:             @response = @response OUTPUT;
35: 
36:         IF @retcode <> 0
37:         BEGIN
38:             RAISERROR('External REST endpoint call failed with status code %d.', 16, 1, @retcode);
39:         END
40: 
41:         SELECT 
42:             @retcode AS StatusCode,
43:             @response AS Response;
44:     END TRY
45:     BEGIN CATCH
46:         DECLARE 
47:             @ErrorMessage NVARCHAR(4000),
48:             @ErrorSeverity INT,
49:             @ErrorState INT;
50: 
51:         SELECT 
52:             @ErrorMessage = ERROR_MESSAGE(),
53:             @ErrorSeverity = ERROR_SEVERITY(),
54:             @ErrorState = ERROR_STATE();
55: 
56:         RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
57:     END CATCH
58: END;
59: GO

Let us break it down line by line:

  • 1, 2: declare idempotent stored procedure name with a single parameter Id, where the definition will not fail if the procedure exists
  • 5: disable returning the affected count of rows
  • 8, 9, 10: declare variable for payload, return code, and response
  • 12 – 16: implement a validation mechanism raising an error in case the row is not found based on the provided Id value
  • 18 – 20: select the JSON data from the RawData column and parse it to NVARCHAR
  • 22 – 26: implement another validation mechanism which raises an error in case the payload is null or empty
  • 28 – 34: call the stored procedure
    • @retcode captures the return code. It will be zero if the API call is executed with HTTPS code 2xx. In case the return code is in the 4xx or 5xx range, the return code is the HTTPS code received
    • @url is the API endpoint being called
    • @payload is the JSON value parsed to NVARCHAR previously selected
    • @credential in this case is null. There is a SAS token part of the API endpoint URL, which is also one reason @headers is not specified.
    • @response has the OUTPUT clause which will serve the API response when the stored procedure executes.
  • 36 – 39: if the HTTPS call was not successful (any other code but 0), we raise an error.
  • 41 – 44: select the return code and API response so they are shown after the stored procedure finishes the execution and wrap up the TRY block
  • 45 – 57: declare the CATCH block and, as good practice, return the error message, severity, and state if any
  • 58: end the stored procedure declaration.
stored procedure to call an api endpoint

Now we can proceed to executing the stored procedure. As an example, I will take row Id 3152, containing Temperature value of 16.80. We expect to see same value returned from the API call:

data preview of row id 3152

Running the stored procedure:

EXEC dbo.usp_ParseTemperatureData @Id = 3152;

Status Code is zero, so it looks OK:

status code and response from stored procedure api call

The operation completed in a little under two seconds:

stored procedure api call status message

Expanding the response, we can see the expected temperature value, nested under the result object:

final response from the API

Conclusion and other scenarios

Parsing or sending data for light transformation outside of SQL Server is not the only scenario when you have the power to call an API endpoint. Here are some other ideas:

  • Chain API calls, such as one call to retrieve an authorization token and another call to use the token
  • GET SQL Server performance data or any telemetry data from an external system
  • Read or write to Azure Blob storage
  • Publish data to Event Hubs from a staging table
  • Refresh a Power BI dataset or implement another software automation with the MS Fabric API
  • Interact with GenAI or any AI service available as a either Azure-hosted API service or self-hosted over HTTPS.

Using the power of sp_invoke_external_rest_endpoint stored procedure in SQL Server 2025, these and many other scenarios are now possible.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *