This put up goes via the steps and processes to Call Web API from SQL Server Stored Procedure. These steps are relevant to Microsoft SQL Server.

 

Pre-Requisites

Initially, to make an HTTP request name Web API from SQL Server Stored Procedure, it’s worthwhile to allow the OLE automation procedures. By default, the OLE automation procedures are disabled in your SQL server instance. Solely a system administrator person can enable entry to OLE Automation procedures through the use of sp_configure.

 

OLE Automation Procedures

There are numerous predefined procedures supplied by the SQL server utilizing which we are able to use to eat API in a saved process:

1) sp_OACreate: sp_OACreate permits you to create an occasion of an OLE object. The newly created OLE object is robotically destroyed on the finish of the Transact-SQL assertion batch.

  • objecttoken OUTPUT is the object returned, and it should be an area variable of information sort int. This object token will establish the created OLE object and might be used to name the opposite OLE Automation stored procedures.

2) sp_OAMethod: sp_OAMethod means that you can name a way of an OLE object.

  • objecttoken is the token of an OLE object created through the use of sp_OACreate.
  • methodname is the tactic title to name.
  • returnvalue OUTPUT is the return worth of the tactic. When specified, it should be an area variable of the suitable knowledge sort.
  • parameter is a technique parameter. When specified, it should be a worth of the suitable knowledge sort.

3) sp_OADestroy: sp_OADestroy will destroy a created OLE object. As talked about within the sp_OACreate technique, If sp_OADestroy is just not known as, the created OLE object will robotically be destroyed on the finish of the batch.

  • objecttoken is the token of an OLE object created through the use of sp_OACreate.

Notice: OLE Automation procedures do present different procedures, however we are going to solely use solely the above three in our instance.

 

Call Web API from SQL Server Stored Procedure

The next script Put together and Ship API request. Request URL, Headers, and Physique are outlined utilizing SQL parameters.

 

Conclusion

There are numerous alternate options once you wish to eat API in your system or put up knowledge to different techniques. Our vote is that the OLE automation process can come in useful once you wish to omit code behind and sync the information into the database. Once you use OLE procedures correctly, sp_OA help permits you to do issues you may’t do one other method. However there are probabilities that you just would possibly end up in a rut.

Call Web API from SQL Server

Leave a Comment