cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stapes
Impactful Individual
Impactful Individual

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

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.

7 REPLIES 7
Pstork1
Dual Super User
Dual Super User

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

 

Capture.JPGCapture1.JPG

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

 

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (3,494)