I am trying to build a flow to run an SQL StoredProcedure that will accept NULL value parameters.
Unfortunately, PowerApps won't.
When I try to test the flow, it tells me these fields require a value.
Wrong! They don't.
Putting values in them will, in this case, give me the wrong results.
How can I specify that these fields are optional.
I have specified each parameter with an Initialize Variable action.
The action to invoke a stored procedure will provide fields for all the parameters whether they are optional or not. For the optional parameters you can just leave them blank and the stored procedure will be invoked with a null value for that parameter, which will trigger it to use the default you have specified in the stored procedure.
I suspect what you mean is that the stored procedure is designed to allow null values on required parameters. For that scenario try feeding in an actual null value using the null expression entered in the expression tab of the dynamic content box.
@Pstork1 can you please provide an example of your 2nd solution suggestion? I believe that is what I need. My stored procedure allows nulls. I just don't know how to get it to work in Flow as an "optional" parameter.
I also can't figure out why Flow is requiring EmployeeId twice...
Flow doesn't understand the concept of an optional parameter. But you can set the parameter to be null when invoking the stored procedure and if the stored procedure allows null parameters it will work fine.
@Pstork1 thank you for the quick reply. How do I set the parameter to be null when invoking from Flow? Ultimately I will call the Flow from PowerApps with the parameters provided from PowerApps.
Use the expressions tab and input null into the field or just leave it blank.
@Pstork1 Is it possible to use an expression to create a case statement? For example, if the parameter is not null from power apps, use the value power apps is providing. Otherwise, use null.
YOu could try coalesce(), but you can't actually pass a null value from Power Apps to Power Automate. You'll have to pass an empty string and work off that.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
At the monthly call, connect with other leaders and find out how community makes your experience even better.
If you are a small business ISV/Reseller, share your thoughts with our research team.