Call a Web API & Web Service from SQL Server

In this blog, we learn to call a Web API from SQL server.

If you have a Web API or a Web Service and want to call from SQL Server to get data in your database, you can do that. In this blog, let's learn how to call a Web API or a Web Service form SQL Server. 

Enable configuration in SQL

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

Sample API link

-- Sample API Link
http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile

Sample JSON Request

@Mobile varchar(max),
@Flag varchar(max)

{
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Content Type

application/json

Create Store Procedure for Calling Web API.

--//========================
    -- if you learn more please visit my blog 

	-- https://saipathrikar.blogspot.com/
--//========================
--================ execute this 1st for 1st time use only

Create Proc Proc_CallApiFromSQL 
(
@Mobile varchar(max),
@Flag varchar(max)
)
as
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 declare @json table (Json_Table nvarchar(max))
 declare @body varchar(max)
 declare @Apilink varchar(max)
 set @Apilink='http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile';
 set @body='

 {
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',@Apilink,'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
 
  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
--select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json))
WITH (
CID bigint,CName varchar(max),AC_Balance varchar(max),AC_No varchar(max),AC_ID bigint,CDOB datetime,
CEmail varchar(max),ReciverEmail varchar(max),CMObile varchar(max),CGender varchar(max),CPan varchar(max),
CAdhaar varchar(max),CNationality varchar(max),CAddress varchar(max),City varchar(max),State varchar(max),
Country  varchar(max),PinCode varchar(max),Cisdelete bit,CreatedBy varchar(max),CreatedDate datetime,ModifiedBy varchar(max),
ModifiedDate datetime,UID bigint,CustImgPath varchar(max),CustAdaarPath varchar(max),CustPanPath varchar(max),
Flag varchar(max),OpMsg varchar(max),Pass varchar(max)
)
return

Execute Stored Procedure 

EXEC  Proc_CallApiFromSQL '8541254874','SE'

Output