Use SQL Server to Call Any Web API & Web Service

First, enable SQL setup, by running below query.

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

A typical API connection link

--Testing API Link 
https://jsonplaceholder.typicode.com/todos/1

Now, below is working SQL scripts to call this sample API, here we are getting data from an open source free API call jsonplaceholder.

create procedure Call_API
as
begin
DECLARE @URL NVARCHAR(MAX) = 'https://jsonplaceholder.typicode.com/todos/1';
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)          
END
ELSE
BEGIN
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
     Print @ErroMsg;
END
Exec sp_OADestroy @Object
end

Now, execute created SP to call API.

exec Call_API

Now, you should be able to see below type of result in query result window, that’s the response from the API that we are calling.

So, like this we can call API in SQL server.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories