Azure Function - HTTP POST Input with SQL binding - How to access POST data?

Question

I'm working on a Javascript Azure Function, triggered by an HTTP POST request, and bound to an Azure SQL server. The function needs to run a Stored Procedure with the POST'ed data, and then return the output from the stored procedure (the row id that was created) to the original caller. I've used the Azure SQL binding for other functions so I know that that's working ok.

My problem is I can't figure out how to connect the POST data to the SQL input binding; when I try I get a lengthy message that makes me believe the parameters are all empty -

Parameters must be separated by "," and parameter name and parameter value must be separated by "=", i.e. "@param1=param1,@param2=param2". To specify a null value, use null, as in "@param1=null,@param2=param2".To specify an empty string as a value, simply do not add anything after the equals sign, as in "@param1=,@param2=param2".

I've tried mapping the inputs to body.userId etc, Body.userId etc, Query.userId etc without success. I also tried using the SQL Output binding, however that doesn't support running a stored procedure or returning data in the response so it's not suitable for this use case.

So how can I get the POST input data to be seen by the HTTP input trigger binding?

function.json -

{  
  "bindings": [  
    {  
      "authLevel": "function",  
      "name": "req",  
      "route": "addSession",  
      "type": "httpTrigger",  
      "direction": "in",  
      "methods": [  
        "post"  
      ]  
    },  
    {  
      "name": "$return",  
      "type": "http",  
      "direction": "out"  
    },  
    {  
      "type": "sql",  
      "name": "results",  
      "direction": "in",  
      "commandText": "SP_AddSession",  
      "commandType": "StoredProcedure",  
      "parameters": "@userId={userId},@browser={browser},@location={location},@platform={platform},@resolution={resolution},@lang={lang}",  
      "connectionStringSetting": "SqlConnectionString"  
    }  
  ]  
}  

Answer 1

We've now given-up on the SQL binding as it's just too restrictive at the moment. For running a simple DB query with one or two inputs it's fine, but anything else is no good. For anyone else who finds this question in the future, the steps required (for a Node function app) were -

1. Install the MSSQL module from NPM on the parent Function App (using a package.json file and Kudu to install, see functions-reference-node )
2. Importing MSSQL in the index.js for each function
3. Create a connection with the parent connection string, submit the request, and make sure to close the connection again to avoid any problems down the line

Example function.json -

{  
  "bindings": [  
    {  
      "authLevel": "function",  
      "type": "httpTrigger",  
      "direction": "in",  
      "name": "req",  
      "route": "testFunction/{queryString}",  
      "methods": [  
        "post"  
      ]  
    },  
    {  
      "type": "http",  
      "direction": "out",  
      "name": "$return"  
    }  
  ]  
}  

index.js -

const sql = require('mssql')  
  
module.exports = async function (context, req) {  
  sql.on('error', err => {  
    sql.close()  
    return {  
      status: 500,  
      body: 'DB connection error'  
    }  
  })  
  
  try {  
    if (req.body) {  
      const pool = await sql.connect(process.env.SqlConnectionString)  
      const result = await pool.request()  
        .input('postProperty1', sql.Int, req.body.property1)  
        .input('postProperty2', sql.NVarChar(128), req.body.property2)  
        .input('queryProperty3', sql.NVarChar(sql.MAX), context.bindingData.queryString)  
        .execute('SP_StoredProcedure')  
  
      sql.close()  
  
      context.res = {  
        status: 200,  
        body: result.recordset  
      }  
    } else {  
      context.res = {  
        status: 400,  
        body: "POST details not found"  
      }  
    }  
  }  
  catch (e) {  
    context.log(e)  
    return {  
      status: 404,  
      body: 'Unexpected error during operation'  
    }  
  }  
}