How can I pass the request body to a stored procedure using Azure Functions JavaScript Model v4

Question

I am using Azure Functions with JavaScript / Node.js model v4 and am trying to pass the request body to a SQL Server stored procedure. I cannot get this to work because retrieval of the request body is asynchronous while the inputs are evaluated immediately.

I have an SqlInput setup like this:

const sp_location_insert = input.sql({
    commandText: 'dbo.sp_location_insert',
    parameters: '@Body={body}',
    commandType: 'StoredProcedure',
    connectionStringSetting: 'SqlConnectionString',
})

and a function defined like this:

app.http('locationPost', {
    methods: ['POST'],
    authLevel: 'anonymous',
    route: 'location',
    extraInputs: [sp_location_insert],
    handler: async (request, context) => {
        const body = await request.json()
        context.log(`body: ${JSON.stringify(body)}`)
        const response = context.extraInputs.get(sp_location_insert)
        const location_id = response[0]["location_id"]
        return {    status: 201,
                    body: location_id
        }
    }
})

The error message that I get is this:

Microsoft.Azure.WebJobs.Host: No value for named parameter 'body'.

I have experimented with many things including removing async and await in the handler, using request.body instead of request.json() and trying to initialize body before awaiting request.json(). Nothing seems to work. I hoped this would work because I have seen this technique work with request parameters and query parameters (in JS v4). The problem seems to be that request body must be awaited for while the extraInput is evaluated as soon as the function is invoked.

Is there a way that I can pass the request body to an SQL Server stored procedure using JavaScript Azure Functions model v4? Microsoft have a 'ToDo' example that achieves this but using the JS v3 function model.

Also asked at: https://stackoverflow.com/questions/77813818/how-to-pass-the-request-body-to-an-azure-sql-stored-procedure-using-azure-functi/77825457?noredirect=1#comment137208823_77825457

Answer 1

Ross Attrill Unfortunately, you cannot use whole body in the binding that way. Instead, refer to JSON payloads section in Azure Function binding, and you can use payloads properties like below for JavaScript.

const sp_sampletest = input.sql({
    commandText: 'dbo.InsertTextAndReturn',
    parameters: '@InputText={sampleJSONBody}',
    commandType: 'StoredProcedure',
    connectionStringSetting: 'SqlConnectionString',
});

app.http('httpTrigger1', {
    methods: ['POST'],
    authLevel: 'anonymous',
    extraInputs: [sp_sampletest],
    handler: async (request, context) => {
        const response = context.extraInputs.get(sp_sampletest);
        return { jsonBody: response };
    }
});

Sample Request/Response:

Note: There are few limitations with respect to payload size, performance etc. and please review the doc for more info. JSON deserialization is automatically performed for JavaScript (in case C#, F#, you need to define a class for de-serialization).

I hope this helps and let me know if you have any questions.

---

If you found the answer to your question helpful, please take a moment to mark it as Yes for others to benefit from your experience. Or simply add a comment tagging me and would be happy to answer your questions.