Calling a Web Service from T-SQL

First you need grand  permission Ole Automation Procedures to your server. This can be achived by executing the below sql script

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

 Then the next step is to call a web call.


create FUNCTION dbo.WebRequest(@parm VARCHAR(20))
RETURNS VARCHAR(250)
AS BEGIN
 DECLARE @parmVARCHAR(20)
 SET @parm= @parm
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
http://www.your.api.call.here', --Your Web Service Url (invoked)

'false'

Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
--Select @ResponseText
Exec sp_OADestroy @Object

    RETURN @ResponseText
END

replace the http://www.your.api.call.here with your web api