Introduction

SQL Server 2025 introduces one of the most forward-looking features in its history: the ability to call external REST and GraphQL endpoints directly from T-SQL using the new stored procedure:

sys.sp_invoke_external_rest_endpoint

This fundamentally expands SQL Server from a relational engine into a hybrid data-integration platform capable of:

• Calling REST and GraphQL APIs
• Triggering Azure Functions or Logic Apps
• Sending data to machine learning models
• Pulling data from external services
• Integrating directly with microservices
• Exchanging JSON and Base64 payloads
• Automating workflows from inside stored procedures

In this article, we’ll break down how it works, how to call authenticated endpoints, how to send and receive JSON, error handling, performance considerations, and real enterprise use cases.

---

What sp_invoke_external_rest_endpoint Does

This new stored procedure makes HTTP(S) calls accessible from inside SQL Server:

Supported methods:

• GET
• POST
• PUT
• PATCH
• DELETE

It accepts:

• URL
• Headers
• Query string parameters
• JSON payloads
• Returned JSON, XML, text, or binary
• Timeouts
• Error metadata

This bridges SQL Server with the outside world without writing CLR code or using xp_cmdshell.

---

Basic Syntax

EXEC sys.sp_invoke_external_rest_endpoint
    @method = 'GET',
    @url = 'https://api.example.com/status';

If the API returns JSON, SQL Server will return it as NVARCHAR.

---

Example: Simple GET Request

EXEC sys.sp_invoke_external_rest_endpoint  
    @method = 'GET',
    @url = 'https://api.github.com';

This is the simplest form.

---

Example: GET with Headers

EXEC sys.sp_invoke_external_rest_endpoint  
    @method = 'GET',
    @url = 'https://api.example.com/data',
    @headers = '{"Accept":"application/json"}';

POST with JSON Payload

DECLARE @payload NVARCHAR(MAX) = N'{
    "requestId": 1025,
    "action": "process"
}';

EXEC sys.sp_invoke_external_rest_endpoint  
    @method = 'POST',
    @url = 'https://api.example.com/ingest',
    @payload = @payload,
    @headers = '{"Content-Type":"application/json"}';

Using BASE64 with REST Calls

From Day 5, Base64 encoding is essential for authentication or binary transfer:

DECLARE @authHeader NVARCHAR(MAX) =
    'Basic ' + BASE64_ENCODE(CONVERT(VARBINARY(200), 'user:password'));

EXEC sys.sp_invoke_external_rest_endpoint  
    @method = 'GET',
    @url = 'https://api.secure-service.com/data',
    @headers = '{"Authorization":"' + @authHeader + '"}';

This is a common pattern when interacting with:

• OAuth services
• API gateways
• Basic-auth protected endpoints
• Machine learning model scoring services

---

Example: Sending JSON From a Table

DECLARE @payload NVARCHAR(MAX);

SELECT @payload = (
    SELECT TOP 1 *
    FROM Orders
    FOR JSON AUTO
);

EXEC sys.sp_invoke_external_rest_endpoint  
    @method = 'POST',
    @url = 'https://api.example.com/order/process',
    @payload = @payload,
    @headers = '{"Content-Type":"application/json"}';

Handling Returned JSON

To handle the result:

DECLARE @response TABLE (JsonText NVARCHAR(MAX));

INSERT INTO @response
EXEC sys.sp_invoke_external_rest_endpoint
    @method='GET',
    @url='https://api.example.com/info';

SELECT * FROM @response;

You can then parse it:

SELECT JSON_VALUE(JsonText, '$.status') AS Status
FROM @response;

Calling Azure Functions

Azure Functions often require POST and authentication.

Example:

EXEC sys.sp_invoke_external_rest_endpoint
    @method='POST',
    @url='https://your-function.azurewebsites.net/api/ProcessOrder',
    @headers='{"x-functions-key":"<your-key>","Content-Type":"application/json"}',
    @payload='{"orderId": 1001, "priority": "high"}';

Calling GraphQL from SQL

Example GraphQL query:

DECLARE @payload NVARCHAR(MAX) = '
{
  "query": "query { product(id: 100) { name price } }"
}';

EXEC sys.sp_invoke_external_rest_endpoint
    @method='POST',
    @url='https://api.example.com/graphql',
    @headers='{"Content-Type":"application/json"}',
    @payload=@payload;

Error Handling

The stored procedure returns structured error responses.

Example TRY/CATCH block:

BEGIN TRY
    EXEC sys.sp_invoke_external_rest_endpoint
        @method='GET',
        @url='https://api.invalid-url.com';
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER()     AS ErrorNumber,
        ERROR_MESSAGE()    AS ErrorMessage,
        ERROR_LINE()       AS ErrorLine;
END CATCH;

Timeouts

You can configure:

@timeout = 30  -- seconds

Default is 30 seconds.

---

Security Considerations

1. Controlled by server configuration

Server admins can disable outbound REST access.

2. Least privilege

Use:

• Proxy identities
• Role-based authentication
• Controlled network rules
• Secure key storage

3. Avoid sending sensitive data in plain text

4. Audit outbound requests

Use Extended Events for tracking.

---

Enterprise Use Cases

1. Real-time data enrichment

Call an external service to enrich data during ETL:

EXEC sys.sp_invoke_external_rest_endpoint
    @method='GET',
    @url='https://api.company.com/geo?ip=198.51.100.4';

2. Send events or logs to cloud monitoring

EXEC sys.sp_invoke_external_rest_endpoint
    @method='POST',
    @url='https://monitoring.company.com/log',
    @payload='{"event":"databaseCheck","status":"ok"}';

3. Machine Learning scoring

EXEC sys.sp_invoke_external_rest_endpoint
    @method='POST',
    @url='https://ml.company.com/score',
    @payload='{"input": [1.2, 3.5, 0.9]}';

4. Trigger an automated workflow

Example using Power Automate or Logic Apps:

EXEC sys.sp_invoke_external_rest_endpoint
    @method='POST',
    @url='https://prod-42.logic.azure.com/workflows/...',
    @payload='{"recordId": 2005}';

5. Push or pull data from microservices

SQL can now participate directly in a service-oriented architecture.

---

Performance Considerations

This feature should be used responsibly.

Good for

• ETL
• Batch processes
• Scheduled jobs
• Controlled server-to-server calls
• Triggering asynchronous workflows

Not good for

• High-frequency OLTP paths
• Per-row calls
• Inside triggers

Always batch your outbound calls.

---

Workshop: Hands-On REST Invocation

Step 1. Simple GET

EXEC sys.sp_invoke_external_rest_endpoint
    @method='GET',
    @url='https://api.github.com';

Step 2. POST payload

EXEC sys.sp_invoke_external_rest_endpoint
@method=’POST’,
@url=’https://httpbin.org/post’,
@payload='{“test”:”Value”}’,
@headers='{“Content-Type”:”application/json”}’;

Step 3. Capture response into a table

DECLARE @response TABLE(JsonText NVARCHAR(MAX));
INSERT INTO @response
EXEC sys.sp_invoke_external_rest_endpoint
    @method='GET',
    @url='https://httpbin.org/get';

SELECT * FROM @response;

Step 4. Parse JSON

DECLARE @auth NVARCHAR(MAX) =
    'Basic ' + BASE64_ENCODE(CONVERT(VARBINARY(200),'user:pass'));

EXEC sys.sp_invoke_external_rest_endpoint
    @method='GET',
    @url='https://httpbin.org/basic-auth/user/pass',
    @headers='{"Authorization":"' + @auth + '"}';

Final Thoughts

SQL Server 2025’s REST invocation feature represents a major shift in how SQL Server participates in modern application architectures. For the first time, SQL can initiate service calls, interact with cloud systems, trigger workflows, and exchange structured JSON with external APIs.

In combination with:

• Native JSON
• JSON indexes
• Base64 functions
• Full RegEx support

SQL Server 2025 becomes far more than a relational engine. It becomes a secure, compliant, cloud-connected integration endpoint capable of orchestrating data flows, analytics, and modern microservice interactions.

This concludes the Advent of 2025 SQL Server Series.

---

References

• sp_invoke_external_rest_endpoint
https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint

• SQL Server 2025 New Features
https://learn.microsoft.com/sql/sql-server/what-s-new-in-sql-server-2025

• REST and JSON Integration
https://learn.microsoft.com/sql/t-sql/json

• Azure Functions and Logic Apps
https://learn.microsoft.com/azure/logic-apps
https://learn.microsoft.com/azure/azure-functions