MS SQL Server routine to call application API methods

We need to call API interface to an application from SQL Server. Our application processed data and stores it in a SQL database. Everything is working as expected, and we are able to execute SQL commands from within our application. We are not able to execute external API/Web services however. We would like to leverage SQL Server to provide this capability, as SQL server can make API/Web Service calls, and we can trigger SQL Server commands, so there is a logical path from our application to the application serviced by the API through SQL Server. This is where we need assistance, developing the SQL Server interface between our application and the other application. The application API is in C or Java, but C interface is slated to be discontinued at some point, so leaning towards Java. Full documentation on API is available, along with sample apps (not in SQL Server though). Functions required: -> Call API that sends data from SQL Server to application. This will be either a create record or an update record. This will be known in advance. I envision calling the SQL Server process, passing the following: a. Instruction: Call Insert API, Call Update API b. Primary Key: c. Data Functions preferred: -> Call API that retrieves data from application in a lookup fashion (i.e.; get details from people form where id = "jones"), and returns to SQL Server. I am not sure what options are available to me, so this can be done in different ways, such as: 1. Return values in an object that the SQL server routine can return to the calling function 2. Place results in a results table that the calling routine can reference 3. Other ways to do this???? I'm open to all suggestions on the best way to do this. --> Call API that retrieves data from application as a list (i.e.; get list of people where status = "active"). Return methodology is open to discussion, as I may not be aware of the best ways to do this in SQL Server. Please respond with any questions if more details are needed. Thanks, Mark