Technical Implementation and Best Practices for Calling APIs from SQL Server Stored Procedures

Introduction

In modern application development, database systems frequently need to integrate with external services. While the traditional approach involves handling API calls at the application layer, calling APIs directly from SQL Server stored procedures can provide a more streamlined architecture and better performance in certain scenarios. This article systematically introduces technical implementation solutions for calling external APIs within the SQL Server environment.

Traditional Method: Using OLE Automation Objects

SQL Server offers a traditional method for calling external services through OLE automation objects. This approach primarily relies on system stored procedures such as sp_OACreate and sp_OAMethod to implement HTTP requests.

Basic Implementation Principles

OLE automation objects allow SQL Server to create and manipulate COM components, where the MSXML2.XMLHTTP or MSXML2.ServerXMLHTTP.6.0 components can be used to send HTTP requests. The following are the basic implementation steps:

First, declare the necessary variables:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Then create the XMLHTTP object and configure the request:

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT',
                 'false'

Send the request and obtain the response:

Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Finally, clean up resources:

Exec sp_OADestroy @Object

Error Handling and Data Parsing

In practical applications, comprehensive error handling mechanisms need to be added. The following example demonstrates how to add error checking and JSON data parsing:

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="Auckland"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;',
                 '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 * 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

Modern Solution: sp_invoke_external_rest_endpoint

SQL Server 2025 introduces the dedicated sp_invoke_external_rest_endpoint stored procedure, providing a more modern and secure solution for calling REST APIs.

Enabling and Basic Usage

First, the external REST endpoint feature needs to be enabled:

EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

Basic call example:

DECLARE @ret AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@ret = sp_invoke_external_rest_endpoint
@url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>?key1=value1',
@headers = N'{"header1":"value_a", "header2":"value2", "header1":"value_b"}',
@payload = N'{"some":{"data":"here"}}',
@response = @response OUTPUT;

SELECT @ret AS ReturnCode,
@response AS Response;

Authentication and Security

Using database-scoped credentials for secure authentication:

CREATE DATABASE SCOPED CREDENTIAL [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<your-function-key-here>"}';

DECLARE @ret AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@ret = sp_invoke_external_rest_endpoint
@url = N'https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>?key1=value1',
@credential = [https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>],
@payload = N'{"some":{"data":"here"}}',
@response = @response OUTPUT;

Technical Considerations and Best Practices

Performance Optimization

When sending large amounts of data, it is recommended to use batching techniques to reduce the number of HTTP calls:

DECLARE @payload AS NVARCHAR (MAX);
SET @payload = (SELECT [object_id],
[name],
[column_id]
FROM sys.columns
FOR JSON AUTO);

DECLARE @retcode AS INT, @response AS NVARCHAR (MAX);
EXECUTE
@retcode = sp_invoke_external_rest_endpoint
@url = '<REST_endpoint>',
@payload = @payload,
@response = @response OUTPUT;

Security Considerations

Calling external APIs at the database level requires special attention to security:

• Implement strong access controls to ensure only authorized users can access sensitive data and REST API endpoints
• Apply the principle of least privilege and configure appropriate database roles and permissions
• Ensure all REST calls are properly authenticated and authorized
• Regularly monitor and audit database access and REST API calls
• Conduct regular security assessments and vulnerability scans

Error Handling and Retry Mechanisms

sp_invoke_external_rest_endpoint supports automatic retry mechanisms:

EXECUTE @returnValue = sp_invoke_external_rest_endpoint
[ @url = ] N'url',
[ @retry_count = ] 3  -- Retry 3 times in case of errors

Architectural Decision Considerations

While calling APIs from stored procedures can be useful in certain scenarios, potential architectural issues should be considered:

• May increase the load on the database server
• External service unavailability may affect database operations
• Debugging and monitoring can be relatively complex
• May violate the design principle of separation of concerns

When deciding whether to call APIs from stored procedures, it is necessary to balance business requirements, performance needs, and architectural complexity.

Conclusion

SQL Server provides multiple technical solutions for calling external APIs from stored procedures. Traditional methods use OLE automation objects, while modern approaches utilize the dedicated sp_invoke_external_rest_endpoint stored procedure. The choice of which solution to use depends on the specific SQL Server version, security requirements, and performance needs. Regardless of the chosen method, critical factors such as error handling, security, and performance optimization must be thoroughly considered.