How do I specify an optional parameter for a Stored Procedure?

(0) Share

Report

Posted on by stapes

894

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.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Justinrr07 44 on at

Like (0)

Report

@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...

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Justinrr07 44 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (1)

Report

Use the expressions tab and input null into the field or just leave it blank.

Was this reply helpful? Yes No
Justinrr07 44 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No