Engineering Core
ISB Vietnam's skilled software engineers deliver high-quality applications, leveraging their extensive experience in developing financial tools, business management systems, medical technology, and mobile/web platforms.

Besides the usual way of calling API from Website or Application, we can call API from SQL Server Stored Process. In this post, I would like to introduce how to call an API from a SQL Server stored procedure by a few steps.

SQL Server doesn't have built-in functionality to directly make HTTP requests, so you'll typically use SQL Server's sp_OACreate and related procedures to interact with COM objects for HTTP requests.

Example using sp_OACreate
Here's a simplified example of how you might use sp_OACreate to call an API from a stored procedure. Please note that this approach relies on the SQL Server's ability to interact with COM objects and may be limited or require additional configuration.

Steps:

1. Enable OLE Automation Procedures:

Before using sp_OACreate, you need to make sure that OLE Automation Procedures are enabled on your SQL Server instance.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'ole automation procedures', 1;

RECONFIGURE;

2. Create the Stored Procedure

Here's an example stored procedure that performs a simple HTTP GET request to an API endpoint.

CREATE PROCEDURE CallApiExample
AS
BEGIN
DECLARE @object INT;
DECLARE @responseText VARCHAR(5000); -- Shoudn't use VARCHAR(MAX)
DECLARE @url VARCHAR(255) = 'https://northwind.vercel.app/api/categories'; -- Replace with your API URL
DECLARE @status INT;
 
-- Create the XMLHTTP object
EXEC sp_OACreate 'MSXML2.XMLHTTP', @object OUTPUT;
 
-- Open the HTTP connection
EXEC sp_OAMethod @object, 'open', NULL, 'GET', @url, 'false';
 
-- Send the request
EXEC sp_OAMethod @object, 'send';
 
-- Get the response text
EXEC sp_OAMethod @object, 'responseText', @responseText OUTPUT;
 
-- Check the status
EXEC sp_OAMethod @object, 'status', @status OUTPUT;
 
-- Get the response text
IF((SELECT @ResponseText) <> '')
BEGIN
DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
PRINT 'Response: ' + @json;
SELECT *
FROM OPENJSON(@json)
  WITH (
id INTEGER '$.id',
description NVARCHAR(MAX) '$.description',
name NVARCHAR(MAX) '$.name'
   );
END
ELSE
BEGIN
DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
PRINT @ErroMsg;
END
 
-- Clean up
EXEC sp_OADestroy @object;
END;


3. Execute the Stored Procedure

Run the stored procedure to see the output:

EXEC CallApiExample;

Result from API:

Result after executing the stored procedure:

Detailed Explanation:
sp_OACreate: This procedure creates an instance of a COM object. Here, 'MSXML2.XMLHTTP' is used to create an object that can make HTTP requests.

sp_OAMethod: This procedure calls methods on the COM object. In this example:

'open' sets up the request method and URL.

'send' sends the HTTP request.

'responseText' retrieves the response body.

'status' retrieves the HTTP status code.

sp_OADestroy: This procedure cleans up and releases the COM object.

 

Considerations:

- Security: Using OLE Automation Procedures can pose security risks. Ensure your SQL Server instance is properly secured and consider using more secure methods if available.

- Error Handling: The example doesn't include detailed error handling. In production code, you should handle potential errors from HTTP requests and COM operations.

- Performance: Making HTTP requests synchronously from SQL Server can impact performance and scalability.

- SQL Server Versions: OLE Automation Procedures are supported in many versions of SQL Server but may be deprecated or not available in future versions. So, please check your version's documentation for specifics.

References:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/ole-automation-stored-procedures-transact-sql?view=sql-server-ver16
https://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure
https://blog.dreamfactory.com/stored-procedures-data-integration-resty-performance/
https://mssqlserver.dev/making-rest-api-call-from-sql-server
Image source: https://www.freepik.com/free-photo/application-programming-interface-hologram_18098426.htm

 

Written by
Author Avatar
Engineering Core
ISB Vietnam's skilled software engineers deliver high-quality applications, leveraging their extensive experience in developing financial tools, business management systems, medical technology, and mobile/web platforms.

Popular Articles

Fintech Development Outsourcing: The Ultimate Guide for 2026
OUTSOURCING

Fintech Development Outsourcing: The Ultimate Guide for 2026

November 25, 2025 · By ISB Vietnam(IVC) Business Development Team

Healthcare IT Outsourcing: A Complete Guide for Medical Software Development
OUTSOURCING

Healthcare IT Outsourcing: A Complete Guide for Medical Software Development

December 29, 2025 · By ISB Vietnam(IVC) Business Development Team

DevOps Best Practices: What to Build and What to Outsource
OUTSOURCING

DevOps Best Practices: What to Build and What to Outsource

March 16, 2026 · By ISB Vietnam(IVC) Business Development Team

What Is Adaptive Software Development (ASD)? A Practical Guide for Modern Engineering Teams
OUTSOURCING

What Is Adaptive Software Development (ASD)? A Practical Guide for Modern Engineering Teams

June 4, 2026 · By ISB Vietnam(IVC) Business Development Team

White Paper

COMPANY PROFILE

Please check out our Company Profile.

Download

COMPANY PORTFOLIO

Explore my work!

Download

ASK ISB Vietnam ABOUT DEVELOPMENT

Let's talk about your project!

Contact US