How To Call APIs From SQL Server

Calling an API has been in Azure SQL since 2022, but it was only recently added to SQL Server 2025 Preview.

This new feature sp_invoke_external_rest_endpoint will allow direct communication with external APIs to send and receive data from within SQL Server. Having this functionality in a stored procedure in SQL Server is a game changer, and it will now be available for on-prem SQL Servers.

The first step is to turn the feature on, and the second is to set up permissions on who can use this.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE;

This is my first try using this new feature. To give you a little background, I organize our local SQL Saturday, and we have developed a badge process that integrates with Eventbrite.

The current process uses Azure Data Factory (ADF) to call Eventbrite APIs to get all of the registrations, but I wanted to see if we could perform the same process now in SQL Server.

I'll walk through each part of the stored procedure and then give you a link to the entire procedure.

We need to pass in two parameters and an optional third. The first is the event ID of the event we are organizing on Eventbrite. The second is the private API Token from Eventbrite. The third is an optional Debug flag to see what's going on if you need to test.

CREATE PROCEDURE stg.EventBriteAPIAttendeesGetAll
    (
        @EventID NVARCHAR(50) = N'',-- Replace with your Eventbrite Event ID
        @Token NVARCHAR(100) = N'', -- Replace with your Eventbrite API token
        @Debug BIT = 0
    )
AS
    BEGIN
        SET NOCOUNT ON;

Eventbrite can only return 50 records for each call, so I created a table to store the results as we loop through all registered attendees. The table includes a timestamp for the overall start time, so I know which calls were all done at the same time, and the individual timestamp of the retrieval to see how long each one took.

IF OBJECT_ID('stg.EventBriteAPIImport', 'U') IS NULL
    BEGIN
        CREATE TABLE stg.EventBriteAPIImport
            (
                ContinuationToken VARCHAR(20) NULL,
                HasMoreItems BIT NOT NULL,
                APIResponse NVARCHAR(MAX) NULL,
                JSONAttendees NVARCHAR(MAX) NULL,
                RetrievedAt DATETIME2(0) NOT NULL CONSTRAINT [DF_EventBriteAPIImport_RetrievedAt] DEFAULT (GETDATE()),
                StartDatetime DATETIME2(0) NOT NULL
            );
    END;

Next, I declare some variables set outside our while loop.

DECLARE
    @Url NVARCHAR(1000),
    @ContinuationToken VARCHAR(20) = NULL,
    @StartDatetime DATETIME2(0) = GETDATE();

Next, we set up a while loop, which will call the Eventbrite API until there are no more records.

WHILE 1 = 1
    BEGIN

We set the calling URL dynamically with the event ID and the token, and then if there is a continuation token, we dynamically add that to the URL.

SET @Url = N'https://www.eventbriteapi.com/v3/events/' + @EventID + N'/attendees/?token=' + @Token;
        IF @ContinuationToken IS NOT NULL
            SET @Url += N'&continuation=' + @ContinuationToken;

		IF (@Debug = 1)
            BEGIN
                PRINT 'Calling URL: ' + @Url;
            END;

Next, we declare variables to capture the API Response, Continuation Token, and then Has More Items.

        DECLARE
            @APIResponse NVARCHAR(MAX),
            @ResultAttendees AS NVARCHAR(MAX),
            @HasMoreItems BIT;

Next, we will execute the stored procedure sp_invoke_external_rest_endpoint located in the msdb database. We pass it the @URL variable, specify it's a GET method along with the content type and set the response output to our previously declared @APIResponse variable.

EXEC msdb.dbo.sp_invoke_external_rest_endpoint
            @url = @Url,
            @method = N'GET',
            @headers = N'{"Content-Type":"application/json"}',
            @response = @APIResponse OUTPUT;

Now, the API Response includes a bunch of information. Below is a sample of the HTTP response format that the stored procedure returns.

{
  "response": {
    "status": {
      "http": {
        "code": "",
        "description": ""
      }
    },
    "headers": {}
  },
  "result": {}
}

Unfortunately, due to the size of some JSON objects and the limitations of SSMS, which only returns a certain length of text, you may not see the entire object string.

To extract the values from the response object, we use the JSON_VALUE expression and map directly to the value from the 'result' object.

SELECT
	@ContinuationToken = JSON_VALUE(@APIResponse, '$.result.pagination.continuation'),
     @HasMoreItems = CAST(JSON_VALUE(@APIResponse, '$.result.pagination.has_more_items') AS BIT);

Next, I want to pull the Attendees from the result object, so I use the OPENJSON function to insert a variable into a separate variable for insertion into our table.

SELECT
	@ResultAttendees = Value
FROM
	OPENJSON(@APIResponse, '$.result')
WHERE
     [Key] = 'attendees';

Next, we insert all of these items into our staging table.

INSERT INTO
	stg.EventBriteAPIImport
     (ContinuationToken, HasMoreItems, APIResponse, JSONAttendees, StartDatetime)
	SELECT
     	@ContinuationToken,
         @HasMoreItems,
         @APIResponse,
         @ResultAttendees,
         @StartDatetime;

Here is a debug section, which shows all of the variables for each call to review if need be.

IF (@Debug = 1)
	BEGIN
		SELECT
         	JSON_VALUE(@APIResponse, '$.result') AS ResultValue,
              JSON_VALUE(@APIResponse, '$.result.pagination.has_more_items') AS HasMoreItems,
              JSON_VALUE(@APIResponse, '$.result.pagination.continuation') AS ContinuationVar,
              JSON_VALUE(@APIResponse, '$.result.pagination.object_count') AS object_count,
              JSON_VALUE(@APIResponse, '$.result.pagination.page_number') AS page_number,
              JSON_VALUE(@APIResponse, '$.result.pagination.page_size') AS page_size,
              JSON_VALUE(@APIResponse, '$.result.pagination.page_count') AS page_count,
              @HasMoreItems AS HasMoreItemsVariable,
              @ContinuationToken AS ContinuationVarVariable,
              @ResultAttendees AS ResultAttendees;
	END;

The next part is where we check the variables for the while loop. If the API response has no more items, we exit the while loop. If it has more items, we continue. There are debug print statements here as well to help troubleshoot.

	IF (@HasMoreItems = 0)
		BEGIN
     		IF (@Debug = 1)
         		BEGIN
              		PRINT 'No more items found. Ending loop.';
				END;
			BREAK;
		END;
	ELSE
		BEGIN
     	IF (@Debug = 1)
         	BEGIN
              	PRINT 'Next continuation token: ' + @ContinuationToken;
              END;
		END;
END;

Here is what our staging table looks like after we make our calls.

As promised, here is a link to the entire stored procedure.

Let me know what you think about this new functionality, and if you have found this useful, leave a comment below.