Ability to Call Azure Function App From Stored Procedure
It would be great if it were possible to call an Azure Function App from a stored procedure.
Certain functionality can be processed much faster via a Function App then via TSQL. (For example, the DW doesn't support recursive CTE; so we use a Function App as it is much faster than a TSQL WHILE loop.)
Our work-around is still faster than the WHILE loop within SQL, but the implementation is awkward. We use a work table that has the "id" and "status" for each task that needs to be processed by the Function App. The stored procedure adds a record to the work table, then loops until the "status" is "complete" then continues. A separate query process within the Function App looks for "status" of "to do" and does the work when necessary.
The extra queries (Function App looking for "to do" tasks and the stored procedure looking for "complete" tasks) could be eliminated with the ability to directly call a Function App.