Azure function parameter binding

Question

I am writing an azure function to take in search parameters in Json format, extract the values from the request body and pass them into a stored procedure. I have been able to do this without SQL binding but I'm trying to do it with SQL binding to reduce the code. However, I am unable to pass the parameters into the stored procedure without errors.

Here is the working code (with SQL binding) that I have written with a hardcoded @caseNo parameter. However, I want to replace the hardcoded with the caseNo that I have in the Case data

`namespace FunctionApp1

{
    public static class Function1
    {
    
    [FunctionName("Function1")]

    public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequest req,              

        [Sql("ETL.CaseSearch",
        commandType: System.Data.CommandType.StoredProcedure,
        parameters: "@caseNo=123456",
        connectionStringSetting: "SqlConnectionString")]
        
        IEnumerable<Object> result,
        ILogger log)
    {

        var receivedContent = await new StreamReader(req.Body).ReadToEndAsync();
        Case data = JsonConvert.DeserializeObject<Case>(receivedContent);

        var jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(result);            

        log.LogInformation("C# HTTP trigger with SQL Input Binding function processed a            request.");            
        

        return new JsonResult(jsonString);


    }
}

}`

Answer 1

You can pass multiple parameters in SQL binding in the following way-

[FunctionName("Function1")]
public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log,
[Sql(commandText: "dbo.SearchCaseNo", commandType: System.Data.CommandType.StoredProcedure,
    parameters: "@CaseNumber={Query.CaseNo},@Name={Query.Name},@City={Query.City}",
    connectionStringSetting: "SqlConnectionString")]
        IEnumerable<Object> result)
{
    log.LogInformation("C# HTTP trigger function processed a request.");

    var receivedContent = await new StreamReader(req.Body).ReadToEndAsync();
    Case data = JsonConvert.DeserializeObject<Case>(receivedContent);

    var jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(result);

    log.LogInformation("C# HTTP trigger with SQL Input Binding function processed a request.");

    return new JsonResult(jsonString);
}

AFAIK, passing case data parameters in SQL binding is not feasible, as binding parameters only accepts string as per ms docs.

Output:

I would suggest you to raise a request with the product team about passing the class data in sql binding parameters.