Allow stored procedures in output bindings

[NathZ1]

chgagnon edit - Changing the title to reflect the root ask, being able to support stored procedures in output bindings.

Hi team,

I am following the documentation to delete a row of data from Azure SQL database, which requires creation of a stored proc which can be triggered with parameters.

https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-azure-sql-input?tabs=in-process&pivots=programming-language-javascript#http-trigger-delete-one-or-multiple-rows-javascript

The big issue I have with this is that I want to do checks prior to the stored proc executing, which I have in the index.ts (using JavaScript). I know I can run checks before adding / updating data in a table using something like this that will conditionally update the discharge table if userAuth returns true (see example below), but there doesn't seem to be something similar I can do for Stored Procs - they execute immediately?

import { AzureFunction, Context, HttpRequest } from "@azure/functions"
import { isUserAuth } from "../utilities"

const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
    const userAuth = await isUserAuth(req.headers.token)

    if (userAuth) context.bindings.discharge = req.body

    //return
    context.res = {
        status: userAuth ? 200 : 403,
        mimetype: "application/json",
        body: userAuth ? req.body : undefined
    };
};

export default httpTrigger;

Is this functionality that can be added, or is there a workaround to achieve the desired result?

Kind regards

[NathZ1]

Ideally, it would be great if we could use Stored Procedures as both input (to run complex SQL scripts that return data) and output (to update / delete data, or even add data using complex logic) bindings. Being able to use Stored Procedures as output bindings would then (hopefully) mean we could execute them conditionally, like we can currently conditionally add/edit data to tables with current output bindings.

[Charles-Gagnon]

You are correct in that input bindings are currently the only way to execute a stored procedure and that those will always run before the actual user function code is executed.

A couple of possible workarounds :

1. Use a C# function with imperitive bindings to execute the binding at runtime based on your criteria
2. Have two functions, one which will be called directly and another which will then conditionally call the function to do the actual deletion

[NathZ1]

Thanks for your advice @Charles-Gagnon re. workarounds. I ended up implementing no. 2. This was relatively straight forward but I did need to update network firewall rules of the Function app to accept the requests coming from 'itself'... This was a bit tricky as couldn't use IPs but got there in the end by adding Azure cloud service tag.

Im not sure on what additional latency is being incurred by the additional function call rather than having conditional SP calls in the single function, but happy for this to be closed as it doesn't seem to be a big issue.

Do you think there would any appetite for improving the functionality at some stage in the future to allow for this use case?

[Charles-Gagnon]

Thanks @NathZ1 - I'll move this into the backlog for now since while it is something that we could consider at this point it's definitely not something we'll be taking for GA given the complexity of rewriting the output binding code to handle stored procedures as well.