How to pass parameter which has = sign in the string to the sql stored procedure in .net 7.0 Isolated Process HttpTrigger azure function?

Question

I am creating an Azure function using .net 7.0 Isolated process which is a Http Trigger. Triggering URL contains 2 parameters and one of them customerKey has '=' sign in the string which is basically an encrypted key passed in the url. These parameters are read and passed to stored procedure. Whenever there is = sign in the customerKey I get run time exception as below from Sql extension.

System.Private.CoreLib: Exception while executing function: Functions.Getcustomers. Microsoft.Azure.WebJobs.Extensions.Sql: 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".

Is there any work around to achieve this?

Thanks.

 [Function("Getcustomers")]
        public static async Task<HttpResponseData>  Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "customers/{customerKey}/{batchsize:int}")] HttpRequestData req,
            ILogger log,
             [SqlInput(commandText: "[sprocname]", commandType: System.Data.CommandType.StoredProcedure, 
                parameters: "@CustomerKey={customerKey},@BatchSize={batchsize}", connectionStringSetting: "SqlConnectionString")]             
                IAsyncEnumerable<customer> customers)
        {
....}

Answer 1

Shridevi Rao Thanks for posting your question in Microsoft Q&A. Parameters cannot contain a comma "," or an equal sign "=" as per doc: Attributes and this is as per design in SQLAttribute which uses [AutoResolve] attribute (only be a string and cannot accept =).

The workaround is to encode customer key (which replaces = as %3d) either while calling Azure Functions via query parameter or in a custom object HttpEncodedBodyObj like described in https://github.com/Azure/azure-functions-sql-extension/issues/302#issuecomment-1235722176 (check out the discussion for custom object) and decode the value in your stored procedure.

Currently we have open item: Find Alternative Way to Pass Parameters to find the approach on handling these scenarios and no ETA yet (still under discussion). I hope the workaround would help with your scenario and let me know if any questions.

---

If you found the answer to your question helpful, please take a moment to mark it as "Yes" for others to benefit from your experience. Or simply add a comment tagging me and would be happy to answer your questions.