Microsoft SQL Server UDF For REST API call

Hello There!

Just continue again what i have done in my previous note. I was writing some UDF for Oracle Database to execute a REST API to CipherTrust Tokenization Server. In this note, i will going to explain how we do REST API at the same way in Microsoft SQL.

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~~ more 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.

1. CONFIGURE MICROSOFT SQL SERVER TO MAKE HTTP REQUEST.

First, you need to enable show advanced options to display the sp_configure system stored procedure advanced options. The default for this configuration is 0 and it means disabled.

Second thing is, you need to enable Ole Automation Procedures. OLE Automation Procedures are disabled by default for new instances of Microsoft SQL Server.

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 / function, enabling you to connect to REST API endpoints.

2. USING PREDEFINED OLE AUTOMATION PROCEDURES

Once you enable OLE Automation Procedures, SQL Server provides several predefined procedures for making API calls. First up is the sp_OACreate method and the second part is sp_OAMethod.

sp_OACreate is a simple method for creating an instance of an OLE object. After running all SQL statements, SQL Server destroys the newly created object. We call this process the completion of the Transact-SQL batch.

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

sp_OAMethod is used to call a method of an OLE object. Please see on the format below:

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

The sp_OADestroy is a procedure that allows you to destroy the object at a different point. But you can ignore this command and no need to call it each time you create a procedure/function, the system would automatically destroy the object at the end of the batch.

3. EXAMPLE TO DO HTTP

Here’s some basic HTTP code that you can execute in Microsoft SQL.

DECLARE @Object as Int;
DECLARE @ResponseText as Varchar(8000);
DECLARE @URI varchar(max) = 'https://your-website.com/list-of-json'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'GET', @URI, 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json; charset=utf-8'
EXEC sp_OAMethod @Object, 'send'
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

SELECT @ResponseText;

As you might see above, Microsoft SQL is not as complicated as Oracle that i had explained here. The sample code provided works well with both HTTP and HTTPS.

If you noticed for sp_OACreate, it is using MSXML2.XMLHTTP that creates an object for XMLHTTP and ask to server for an XML document then the server sends back an XML document to its client.

4. HTTP JSON IN MICROSOFT SQL

Ok, so how about JSON body? is it possible to send it via Microsoft SQL UDF? the answer is : of course yes! Please see below.

DECLARE @URL NVARCHAR(MAX) = 'https://your_domain.com/updatecustomer';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
   "personId": 1,
   "firstName": "Sandy"
   "lastName": "Haryono" 
}'
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

5. CREATING AN UDF

This is interesting part, to avoid repetition, now we are going to create all of above explanation into one function. Let say, we create the function name is dbo.getPersonData() which would return a JSON data that shows lot of data contains person data.

The parameter that need to be send to server is personId, firstName and lastName and the variables are @PersonId, @firstName and @lastName.

Here is the sample of JSON and it has a structure as below :


--- Request ---
{
   "personId":  @PersonId,
   "firstName": @firstName,
   "lastName": @lastName 
}

--- Response ---

{
   "personId":  1,
   "firstName": "Sandy",
   "lastName": "Haryono",
   "address":  "Jl. Boulevard no.46 RT.3 RW.13 Jakarta Selatan",
   "phone" : {
          "mobile" : "+628180088080",
          "home"   : "+622180800028"
    }
   
}

So, the complete code would like below :

CREATE FUNCTION dbo.getPersonData(@PersonId int, @FirstName varchar(100), @LastName varchar(100))
RETURNS varchar(MAX)
AS
-- Returns the information for the person's object.
BEGIN
    
DECLARE @URL NVARCHAR(MAX) = 'https://your_domain.com/updatecustomer';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
   "personId": @PersonId,
   "firstName": @FirstName,
   "lastName": @LastName  
}'
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
RETURN @ResponseText;

END;

Or even, you might change the way code is something more neat :

CREATE FUNCTION dbo.getPersonData(@PersonId int, @FirstName varchar(100), @LastName varchar(100))
RETURNS varchar(MAX)
AS
-- Returns the information for the person's object.
BEGIN
    
DECLARE @URL NVARCHAR(MAX) = 'https://your_domain.com/updatecustomer';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) = (SELECT 
					@PersonId As personId,      -- your person id
					@FirstName As firstName,    -- your first name
					@LastName As lastName       -- your last name
				FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);

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
RETURN @ResponseText;

    
END;

Or maybe you are only need to get “address” value only, here’s an example of what you could do :

From :

RETURN @ResponseText;

Into :

RETURN JSON_VALUE(@ResponseText, '$.address')

6. LAST BUT NOT LEAST, CALLING AN UDF

SELECT TOP 1 dbo.getPersonData(1, "Sandy", "Haryono") as PersonAddress FROM persons;

Finally, you have a UDF that interacts with a third party through a REST API from your Microsoft SQL server.

That’s all folks! Happy coding! 🥰😍

^{Note : Tested on MSSQL 2016 and 2019}

Sandy's simple note

My Insight