How to use parameter in Azure sql_input with LIKE SQL command

Question

Working in V2 Python Cloud Function in Azure, I am trying to implement a parameter from URL to a query to Azure SQL database. I am unable to do so when using SQL LIKE.

I have managed to set up a connection to the database. I also managed to use parameters from URL within the query using the decorators:

@app.route("select_name/{name_string}")
@app.sql_input(arg_name="people",
                        command_text="SELECT * FROM dbo.test WHERE name = @Name",
                        command_type="Text",
                        parameters="@Name={name_string}",
                        connection_string_setting="SqlConnectionString")

However, when I try to change the query from WHERE TO LIKE and combine it with UPPER like this:

@app.route("select_name/{name_string}")
@app.sql_input(arg_name="people",
                        command_text="SELECT * FROM dbo.test WHERE UPPER(name) LIKE UPPER('%@Name%')",
                        command_type="Text",
                        parameters="@Name={name_string}",
                        connection_string_setting="SqlConnectionString")

... I get the error description:

System.Private.CoreLib: Exception while executing function: Functions.get_people_by_name. Microsoft.Azure.WebJobs.Host: '%@Name%' does not resolve to a value.

I understand that parsing variables within SQL query does not want to work with such syntax.

How can I fix this? And maybe there more flexible methos for querying Azure SQL from a cloud function? Maybe you would recommend more python-based solutions, not necessarily relying on decorators?

Meanwhile, if you can point me to some good resources related to doing SQL queries from cloud function and constructing APIs, it would be much appreciated. I have found Azure Documentation not easy to work with and would love to find some good resources elsewhere.

Avilon · Accepted Answer · 2024-03-20 23:09:20Z

0

You should be able to use something like SELECT * FROM dbo.test WHERE UPPER(name) LIKE UPPER('%' + @Name + '%')

answered Mar 20, 2024 at 23:09

Avilon

3015 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jarekko Over a year ago

Not working. It returns

System.Private.CoreLib: Exception while executing function: Functions.get_people_by_name. Microsoft.Azure.WebJobs.Host: '%' + @Name + '%' does not resolve to a value

. This parameter with @ does not behave like a regular Python variable.

Collectives™ on Stack Overflow

How to use parameter in Azure sql_input with LIKE SQL command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related