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

HostForLIFEASP.NET SQL Server 2019 Hosting