Calling an API from SQL Server Stored Procedure

Calling an API from SQL Server stored procedure

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

Can I call a Web API from SQL Server?

Sure it's as simple as this;

     exec  [dbo].[APICaller_POST]
     @URL = 'http://localhost:5000/api/auth/login'
     ,@BodyJson = '{"Username":"gdiaz","Password":"password"}'

After you deploy this CLR Stored procedure:
https://github.com/geral2/SQL-APIConsumer

It has multiple procedures that allows you calling API that required a parameters or even passing multiples headers and tokens authentications.

Calling an API from a stored procedure

I could call any HTTP but not HTTPS from the procedure due to the missing Client Certificate. I copied the certificate from the browser and the call was successful. Postman and browsers have built-in function to acquire the certificates.

But in any case straight HTTP calls from procedures should be discouraged as it is mentioned on the comments here.

How to call web API in MS SQL stored procedure

Try the following sp

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


    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://your-api-address', --Your Web Service Url (invoked)
                 'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    Select @ResponseText

    Exec sp_OADestroy @Object

Call a Web Api 2 endpoint from a stored procedure

The best way would be to create Used-defined CLR function and call your Web API from there, so you can use full power of C# and .Net libraries to do web calls and parse Json. There is plenty of information on the internet about that. For example: https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/. This is not about WebAPI in particular, but you can get the idea from there.

Please note that it would require deployment of your custom assembly with CLR Function(s) to the SQL Server.

Edit:

There is a way to do it in TSQL using OLE Automation. See an example here, but it is is much harder, less documented and you will probably spend time inventing you own bicycle instead of using ready solutions from with CLR functions.

Call stored procedure to input data in database from ASP.NET Web API?

since you dont expect any data to return, try this

_context.Database.ExecuteSqlCommand(result);

or async with params

var resultParam = new SqlParameter("@result", result);
await   context.Database.ExecuteSqlCommandAsync("EXEC  sp_add @result", resultParam);

How to execute a stored procedure in Web API and return a list

try this

 var response = new  List<SalesAndReturns_RPT>();

   using (var reader = cmd.ExecuteReader())
    {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var sales= new SalesAndReturns_RPT();
                     sales.BusinessArea = Convert.ToDecimal(reader.GetString(0));
                    sales.NetValue  = Convert.ToDecimal(reader.GetDecimal(1));
                
                 response.Add(sales);
                  
                 }
                
            }
     }
     return response;

---

Related Topics