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

 

Leave a comment

*