Calling REST API From SQL Server Stored Procedure

4 min readOct 27, 2020

Calling REST API From SQL Server Stored Procedure

Brief

Most of us run into a challenging situation where we need to pull the data from a third-party system, sync it into our database, or pass data from our database to the other system. The most common solution to communicate with other systems is to provide the essentials data using API. The APIs are standard, scalable, and platform-independent, making communication and data transfer processes easy between two systems.

In many scenarios, we have to process the data received from API and then insert/modify data as per our requirement; this is where calling API from the SQL server database can come in handy.

Let’s have a look at how we can do that!

Configuration

First of all, to make an HTTP request call from a stored procedure, you need to enable the OLE automation procedures. By default, the OLE automation procedures are disabled on your SQL server instance. Only a system administrator user can allow access to OLE Automation procedures by using sp_configure.

Use the below line of code to enable the OLE automation procedures.

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

OLE Automation Procedures

There are various predefined procedures provided by SQL server using which we can use to consume API in a stored procedure:

1) sp_OACreate
sp_OACreate lets you create an instance of an OLE object. The newly created OLE object is automatically destroyed at the end of the Transact-SQL statement batch.

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

objecttoken OUTPUT is the object returned, and it must be a local variable of data type int. This object token will identify the created OLE object and will be used to call the other OLE Automation stored procedures.

2) sp_OAMethod
sp_OAMethod allows you to call a method of an OLE object.

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

objecttoken is the token of an OLE object created by using sp_OACreate.
methodname is the method name to call.
returnvalue OUTPUT is the return value of the method. When specified, it must be a local variable of the appropriate data type.
parameter is a method parameter. When specified, it must be a value of the appropriate data type.

3) sp_OADestroy
sp_OADestroy will destroy a created OLE object. As mentioned in the sp_OACreate method, If sp_OADestroy is not called, the created OLE object will automatically be destroyed at the end of the batch.

sp_OADestroy objecttoken

objecttoken is the token of an OLE object created by using sp_OACreate.

Note: OLE Automation procedures do provide other procedures, but we will only use only the above three in our example.

Example

We will use Microsoft’s Northwind database for this example. You can create or use any existing API methods on any database you prefer.

1) GET method
Suppose you want to fetch a list of all employees, you need to follow the below steps:

Create the URL
Pass the parameters if required
Create an OLE object using the sp_OACreate procedure
Pass the created OLE object and make an HTTP request call
Handle the response received from API
Parse the JSON records and insert/ update in the desired table

DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/employees/getemployees';
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 (
                 EmployeeName NVARCHAR(30) '$.employeeName',
                 Title NVARCHAR(50) '$.title',
                 BirthDate NVARCHAR(50) '$.birthDate',
                 HireDate NVARCHAR(50) '$.hireDate',
                 Address NVARCHAR(50) '$.address',
                 City NVARCHAR(50) '$.city',
                 Region NVARCHAR(50) '$.region',
                 PostalCode NVARCHAR(50) '$.postalCode',
                 Country NVARCHAR(50) '$.country',
                 HomePhone NVARCHAR(50) '$.homePhone'
               );
END
ELSE
BEGIN
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
     Print @ErroMsg;
END
Exec sp_OADestroy @Object

Note: OPENJSON is supported by SQL Server 2016 and higher versions.

If you want to pass parameters in the above GET method, you can pass it like below or use a SET command to append the string variable.

DECLARE @EmployeeId INT = 1;
DECLARE @URL NVARCHAR(MAX) = CONCAT('http://localhost:8091/api/v1/employees/', @EmployeeId);

2) POST method
To update the employee record, you need to follow the below steps:

Create the URL
Pass the parameters. In the below example, I have used static JSON, but you can create the dynamic JSON response from table data and then pass it in the API
Create an OLE object using the sp_OACreate procedure
Pass the created OLE object and make an HTTP request call
Handle the response received from API

DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/employees/updateemployee';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
   "employeeId": 1,
   "firstName": "Nancy",
   "lastName": "Davolio",
   "title": "Sales Representative",
   "birthDate": "2020-08-18T00:00:00.000",
   "hireDate": "2020-08-18T00:00:00.000",
   "address": "507 - 20th Ave. E. Apt. 2A",
   "city": "Seattle",
   "region": "WA",
   "postalCode": "98122",
   "country": "USA",
   "homePhone": "(206) 555-9857"
}'
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 'Employee Details'
END
EXEC sp_OADestroy @Object

Conclusion

There are various alternatives when you want to consume API in your system or post data to other systems. Our vote is that the OLE automation procedure can come in handy when you want to omit code behind and sync the data into the database. When you use OLE procedures wisely, sp_OA support lets you do things you can’t do another way. But there are chances that you might find yourself in a rut. You can always reach out to a proficient asp.net core development company, like ZealousWeb to help you swiftly plan, implement, and monitor tasks related to web administration.

Originally published at https://www.zealousweb.com.