How to use Less than and greater than query parameters with Input Binding in SQL Database

Question

I'm using input binding to display a sample SQL database table through an API, and I've got it working to return the entire table. Now, I'm trying to allow users to input their own query parameters. I want to know if users can use query parameters with greater than or less than with input binding. So far, I've only seen users assign query parameters to specific values instead of having a range of values using query parameters. Thank you in advance!

Answer 1

Hunter B Here are the function details that I have tested in my Isolated function:

HttpTrigger1.cs

public class ToDoItem
    {
        public Guid Id { get; set; }
        public int? order { get; set; }
        public string title { get; set; }
        public string url { get; set; }
        public bool? completed { get; set; }
    }

    public class HttpTrigger1
    {
        private readonly ILogger _logger;

        public HttpTrigger1(ILoggerFactory loggerFactory)
        {
            _logger = loggerFactory.CreateLogger<HttpTrigger1>();
        }

        [Function("HttpTrigger1")]
        public HttpResponseData Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req, 
                                        [SqlInput(commandText: "select [Id], [order], [title], [url], [completed] from dbo.ToDo where [order] > @param1 and [order] < @param2",
                                            commandType: System.Data.CommandType.Text,
                                            parameters: "@param1={Query.param1},@param2={Query.param2}",
                                            connectionStringSetting: "SqlConnectionString")]IEnumerable<ToDoItem> toDoItem)
        {
            _logger.LogInformation("C# HTTP trigger function processed a request.");

            var response = req.CreateResponse(HttpStatusCode.OK);
            response.Headers.Add("Content-Type", "text/plain; charset=utf-8");

            response.WriteString("Welcome to Azure Functions!");

            return response;
        }
    }

Sample DB data:

Query:

http://localhost:7071/api/HttpTrigger1?param1=120&param2=125

Output:

You can use this as reference and check out more samples available https://github.com/Azure/azure-functions-sql-extension/tree/main/samples/samples-outofproc/InputBindingSamples. I hope this helps. If you face any issues, let me know.