Making REST-API call from SQL Server procedure

Recently I had a requirement, wherein I had to import the data from one of the internal applications to the SQL Server for further processing. We had REST-API open at a particular endpoint so any other service can consume the API and fetch data for inserting/updating into tables in SQL Server.

For this purpose, I ended up creating a stored procedure using the OLE Automation procedure, which will make an HTTP request call and we can be able to perform GET and POST request methods.

OLE Automation Procedures are extended stored procedures allowing users to execute external functions to SQL Server. These can perform use cases like making HTTP calls, Reading and Writing files, Accessing File System objects, etc. The use of these extended procedures is sensitive and should be closely monitored.

The full set of OLE automation procedures are:

• sp_OACreate

• sp_OAGetProperty

• sp_OASetProperty

• sp_OAMethod

• sp_OAGetErrorInfo

• sp_OAStop

• sp_OADestroy

By default, the OLE Automation is disabled in SQL Server and has to be enabled. Below is the script to check and enable OLE Automation.

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

Let's continue forward with our demonstration.

GET Method call:

Below is the script I used in the stored procedure, for making HTTP call of GET method request type.

DECLARE @user_id int = 2; -- input parameter, to be passed with GET Request
DECLARE @URL NVARCHAR(MAX) = concat('https://reqres.in/api/users/', @user_id); -- our URL for http call
print '@URL -> '+ @URL;
Declare @Object as Int; -- declare object
Declare @ResponseText as Varchar(8000);

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; -- creating OLE object and assigning it to variable @Object
-- print @Object; -- 16711422

-- passing the @Object created above, with our http call and handling the response with help of sp_OAMethod 
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)
     print(@json); -- printing the json output

     select 
        rs.id, rs.email, rs.first_name, rs.last_name, rs.avatar
     from openjson(@json, '$.data') with  -- only parsing data json array object of the responseText
     (
        id int '$.id',
        email varchar(200) '$.email',
        first_name varchar(200) '$.first_name',
        last_name varchar(200) '$.last_name',
        avatar varchar(255) '$.avatar'
     ) rs; -- select query to further process the data.
END
ELSE
BEGIN
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
     Print @ErroMsg;
END
Exec sp_OADestroy @Object; -- destory the object when not needed anymore. If not called, the OLE object will be destroyed automatically when the batch execution is completed.

POST Method call:

Below is the script, which can be used in the stored procedure, for making HTTP calls of POST method request type.

DECLARE @URL NVARCHAR(MAX) = 'https://reqres.in/api/users/'; -- our URL for post request
DECLARE @Object AS INT; -- object declaration
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =

'{
    "name": "kiran sabne",
    "job": "leader"
}';
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; -- creating OLE object and assigning it to variable @Object

-- passing the @Object created above, with our http call and handling the response with help of sp_OAMethod 
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
-- print 'responseText -> ' + @ResponseText;
IF CHARINDEX('false',(SELECT @ResponseText)) > 0
BEGIN
 SELECT @ResponseText As 'Message'
END
ELSE
BEGIN
 SELECT @ResponseText As 'New User Added Detail'
END
EXEC sp_OADestroy @Object

Summary

As mentioned earlier, the usage of OLE Procedures is sensitive and must be strictly monitored. Similarly, we can also perform File Object modifications with the OLE procedures. The other obvious and preferred alternatives are creating another application server for doing work and that application having access to the database.