SQL Server Stored Procedures: Best REST API Calls for Data Integration

by Spencer Nguyen • September 3, 2024

System administrators, DBAs, and application developers all know about the need for data integration. That’s when you have to pull data from one database, application, or web service to another. Fortunately, this task is a lot less challenging than it used to be because APIs (application programming interfaces) are so popular today. APIs provide a standard way to share the data and services of one system with another, even if the two systems are otherwise incompatible.

DreamFactory_blog_CTA_163x200@2x-May-07-2024-08-15-34-3229-AM

The REST API protocol in particular is frequently used for data integration needs. It’s easy to create API calls using RESTful principles and send data back and forth in JSON, a data format similar to XML but more versatile. And when it comes to storing data, Microsoft SQL Server has been widely adopted by enterprises everywhere.

SQL Server stored procedures make it easy to run code on the database when needed. Developers with data integration needs have worked out many ways to do data integration with REST API code and SQL Server stored procedures. Here’s a look at some of the best REST API calls for data integration.

Here's the key things to know about SQL Server Stored Procedures:

Stored Procedures: These are like small programs run on a SQL Server database, capable of accepting input parameters and returning multiple values, including status values to indicate success or failure.
SQL Server Configuration for HTTP Requests: To make REST API calls from stored procedures, SQL Server needs configuration to enable HTTP requests using OLE Automation Procedures.
Using OLE Automation Procedures: SQL Server offers predefined procedures like sp_OACreate, sp_OAMethod, and sp_OADestroy for making API calls, allowing the creation, method execution, and destruction of OLE objects.
REST API with GET and POST Methods: Stored procedures can be used to call REST API endpoints, both for retrieving (GET) and sending (POST) data, handling API responses in JSON format and updating SQL Server tables.
Stored Procedures and REST APIs Limitations: While useful for simple data transfers, more complex tasks might require advanced scripting languages. API management platforms can offer a more scalable, secure, and efficient solution for SQL Server data integration needs.

How Stored Procedures and REST APIs Work Together

Stored procedures are like small programs you can run on a SQL Server database. According to Microsoft, stored procedures can:

Accept input parameters and return multiple values in the form of output parameters to the calling program.
Contain programming statements that perform operations in the database. These include calling other procedures.
Return a status value to a calling program to indicate success or failure (and the reason for failure).

Like other small programming languages, stored procedures are perfect for running bits of code on a regular basis. Repetitive and regularly scheduled tasks are handled easily by stored procedures. They’re also handled more efficiently, as each stored procedure is compiled the first time it’s run. This means they run fast and use less overhead on the server.

Configure SQL Server To Make HTTP Requests

Most data integration tasks will need to be run more than once. And since you can use code in stored procedures, REST API calls are a perfect fit for stored procedures. Before you get started though, you need to properly configure SQL Server to make HTTP requests from within stored procedures. That’s because RESTful APIs are built upon the HTTP commands that power web services.

To enable HTTP requests, an administrator will need to enable OLE Automation Procedures on the SQL Server instance. An administrator with the appropriate permissions needs to run these commands:

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

Now you can make HTTP requests from within stored procedures, enabling you to connect to REST API endpoints.

Using Predefined OLE Automation Procedures

Once OLE Automation Procedures have been enabled, SQL Server offers a few predefined procedures that can be used for making API calls. First up is the sp_OACreate method.

sp_OACreate

sp_OACreate is a simple method for creating an instance of an OLE object. The newly created object is destroyed after all SQL statements have been run. In SQL Server terms, this is known as the completion of the Transact-SQL batch.

The format for sp_OACreate looks like this:

sp_OACreate { progid | clsid } , objecttoken OUTPUT [ , context ]

The variable OUTPUT is the returned object. It will identify the newly created OLE object and be used to call other OLE Automation stored procedures.

sp_OAMethod

sp_OAMethod is used to call a method of an OLE object. Its format follows this convention:

sp_OAMethod objecttoken , methodname
[ , returnvalue OUTPUT ]
[ , [ @parametername = ] parameter [ OUTPUT ] [ ...n ] ]

Here’s what each part of this code means:

objecttoken is the token of the OLE object created earlier with sp_OACreate.
methodname is the name of the method to be called.
returnvalue OUTPUT is the object for the returned value of the method.
parameter is a method parameter.

sp_OADestroy

Each OLE object is destroyed at the end of a Transact-SQL batch. The sp_OADestroy procedure allows you to destroy the object at a different point. Here’s how to call this procedure:

sp_OADestroy objecttoken

objecttoken is, of course, the OLE object created earlier by sp_OACreate.

REST API in SQL Server Stored Procedure: The GET Method

Now that you’re familiar with the OLE predefined procedures, you can put them to use by calling a REST API endpoint in a stored procedure. Here’s an example using the HTTP GET method for retrieving customer data from an API endpoint:

DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/customers/getcustomers';
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
       @URL,
       'False'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF((Select @ResponseText) <> '')
BEGIN
     DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
     SELECT *
     FROM OPENJSON(@json)
          WITH (
                 customerName NVARCHAR(30) '$.customerName',
                 Title NVARCHAR(50) '$.title',
                 Address NVARCHAR(50) '$.address',
                 City NVARCHAR(50) '$.city',
                 Region NVARCHAR(50) '$.region',
                 PostalCode NVARCHAR(50) '$.postalCode',
                 Country NVARCHAR(50) '$.country',
                 Phone NVARCHAR(50) '$Phone'
               );
END
ELSE
BEGIN
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
     Print @ErroMsg;
END
Exec sp_OADestroy @Object

This stored procedure code does the following:

Create the API’s URL
Create an OLE object with the sp_OACreate procedure
Pass the OLE object to an HTTP request call
Accept the API’s response in JSON format
Parse the JSON data and insert (or update) records in the customer table

Now that you’ve created a stored procedure to retrieve data via a REST API, the next part of this tutorial is how to do the same with sending data to an API endpoint.

REST API in SQL Server Stored Procedure: The POST Method

Sending data to an API endpoint using the POST HTTP method is similar to doing the reverse with the GET method. The main difference is that you can issue SQL SELECT statements to retrieve the data to send, or you can include static JSON in your stored procedure. This example uses static JSON to create an OLE object:

DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/customers/updatecustomer';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
   "customerId": 1,
   "firstName": "John"
   "lastName": "Davis", 
   "title": "Store Manager",
   "address": "123 Main Street",
   "city": "St. Louis",
   "region": "MO",
   "postalCode": "63101",
   "country": "USA",
   "homePhone": "(555) 555-8746"
}'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post',
                 @URL,
                 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF CHARINDEX('false',(SELECT @ResponseText)) > 0
BEGIN
 SELECT @ResponseText As 'Message'
END
ELSE
BEGIN
 SELECT @ResponseText As 'customer Details'
END
EXEC sp_OADestroy @Object

And that’s how you use OLE objects—and their support for HTTP requests—to utilize REST APIs in stored procedures.

Stored Procedures and REST APIs: Features and Limitations

For simple data transfer needs, like updating reporting dashboards with data from SQL Server tables, the OLE automation procedures work well. The SQL Server stored procedure language has support for functions that you can use for other API functions, such as parameters, authentication with an API key or OAuth, or advanced HTTP headers.

However, more complex tasks might call for the use of a more advanced scripting language than the stored procedures language. You might consider Python or other languages that have good support for working with Javascript/JSON and SQL server schemas like Java.

Java has strong integration with SQL Server, thanks to the many features of the JDBC driver. On the other hand, Java has a high overhead in terms of system resources—and the time it takes to learn the connection string format of JDBC.

Migrating Legacy Data Integration Processes to REST APIs

Migrating legacy data integration processes to REST APIs offers a modern approach to data exchange, enhancing scalability, real-time access, and flexibility. Traditional methods like flat file transfers and ETL (Extract, Transform, Load) processes often rely on batch operations, which can introduce latency and complexity in data pipelines. By transitioning to REST API-based integration, you can achieve more efficient and responsive data handling within your SQL Server environment.

Key Steps in the Migration Process

1. Assess Current Integration Workflows:

Identify all legacy processes, such as scheduled flat file transfers or ETL jobs, that need modernization.
Analyze the data sources, formats, and frequency of these processes to determine the required API endpoints.

2. Design RESTful Endpoints:

Define RESTful API endpoints that replicate the functionality of existing processes, ensuring they handle the necessary CRUD operations (Create, Read, Update, Delete).
For data previously handled by ETL processes, design APIs that allow direct access to source systems, reducing the need for intermediate staging.

3. Modify SQL Server Stored Procedures:

Update or create new stored procedures in SQL Server to interact with the designed REST APIs.
Use SQL Server’s OLE Automation Procedures (e.g., sp_OACreate, sp_OAMethod) to make HTTP requests, replacing file-based operations with API calls.

4. Handle Data Transformation:

Incorporate data transformation logic directly within stored procedures or through API responses, eliminating the need for separate ETL tools.
Use JSON functions in SQL Server to parse and manipulate API responses, converting data into the required format.

5. Automate and Schedule API Calls:

Utilize SQL Server Agent to schedule stored procedures that call the REST APIs, ensuring regular and automated data synchronization.
Replace legacy job schedulers with SQL Server Agent tasks, maintaining the existing automation but with modernized data flows.

6. Ensure Data Integrity and Security:

Implement robust error handling in stored procedures to manage API response failures or data discrepancies.
Secure API calls with appropriate authentication methods (e.g., API keys, OAuth), replacing unsecured file transfers with encrypted API transactions.

7. Test and Validate:

Perform comprehensive testing to ensure that the new API-based processes replicate or improve upon the functionality of the legacy systems.
Validate that data accuracy, completeness, and performance meet or exceed the standards of the old processes.

Benefits of REST API - Based Integration

REST API-based integration offers immediate data access, minimizing latency compared to batch processing. It enhances scalability by facilitating seamless integration with cloud services and distributed systems. The consolidation of data integration into API calls simplifies system architecture, reducing maintenance complexity. Additionally, it improves security through modern authentication methods and encrypted communications, surpassing the protection offered by legacy file-based approaches.

API Management Platforms and SQL Server

An easier and more full-featured way to take care of your SQL Server data integration needs is an API management platform. These comprehensive apps offer several advantages when working with REST APIs and SQL Server.

First, an API management platform is more scalable. Since the platform provides an abstraction layer between SQL Server and web services, the workload on your database is greatly reduced. Management platforms tend to offer more security than opening up your SQL server instance to all the connections required for stored procedures or other hand-coded solutions. And that's just one of many benefits of these platforms.

DreamFactory: A Complete Management Platform for SQL Server (And All Your APIs)

DreamFactory has excellent data integration capabilities with SQL Server. Generating and documenting a SQL Server API takes less than five minutes. That’s much less than the time it takes to create stored procedures, test them out, and manually document your API. And DreamFactory can handle more than just SQL Server. It’s the complete platform for all your data sources and API management needs. The DreamFactory iPaaS (Internet Platform as a Service) allows you to get up and running right away. You can even get a 14-day free trial to try it out for yourself. Sign up today to see why DreamFactory is the only API management platform you need.