Find Alternative Way to Pass Parameters

[sophiatev]

The way a user currently passes parameters to the SQL query included in their input binding is via the Parameters parameter of the SqlAttribute. This isn't a great way to do it, for one because it doesn't allow for parameter names or values that include commas and equal signs, and also because it's not a very intuitive experience for the user. Perhaps we can look into how CosmosDB does parameters - sounds like they implemented their own version of "auto resolve"?

[mark-comeau]

Hi,
A couple of points I want to throw out so that they're on your radar:
Sql performance is very sensitive to the correct type definition of parameter. A stored procedure parameter defined as NVARCHAR(20) should be explicitly defined in a SqlCommand with SqlDbType = SqlDbType.NVarChar and Size = 20.
Regarding Azure Sql specifically, it is recommended to make a few calls as necessary to the database, which is easily achieved with table-valued parameters. Instead of making 100 calls to insert data you can make 1 call and pass it a table-valued parameter that has 100 rows of data. Supporting table-valued parameters is a must.
Great library, this will be a wonderful addition to Functions!

[kunalkankariya]

Does this mean, we can't use a query like ? SELECT * FROM [dbo].[tablename] where Id in (@id) and pass parameter like Parameters = "@id ={Query.id}" parameter is passed to function like this GET http://localhost:7071/api/xyzz?id=1,2

Can someone please confirm? As this doesn't seem to be working for me. I get the following error

System.Private.CoreLib: Exception while executing function: ScannerSites. azure-sql-binding-func-dotnet-todo: Object reference not set to an instance of an object.

Thanks!

[Charles-Gagnon]

I wonder if a ResolutionPolicy would help here. CosmosDB does this for parsing out parameters : https://github.com/Azure/azure-webjobs-sdk-extensions/blob/dev/src/WebJobs.Extensions.CosmosDB/CosmosDBSqlResolutionPolicy.cs

[MaddyDev]

Related #943