sql-serverstored-procedures

Calling an API from SQL Server stored procedure

Calling an API from an ASP.NET Web Form is very easy.

WebClient wc = new WebClient();
string urlData = wc.DownloadString("http://x.x.x.x/path/api.do?ID=test");

But can we call an API from a SQL Server stored procedure?

If yes, then how can we get the API response into SQL Server?

Solution

  • Please see a link for more details. 

    Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object