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

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. 

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.

Table of Contents

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.

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.

Related Reading:

Spencer Nguyen

As a seasoned content moderator with a keen eye for detail and a passion for upholding the highest standards of quality and integrity in all of their work, Spencer Nguyen brings a professional yet empathetic approach to every task.