cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stapes
Post Prodigy
Post Prodigy

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 III
Dual Super User III

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

Pstork1
Dual Super User III
Dual Super User III

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.

Pstork1
Dual Super User III
Dual Super User III

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.

Pstork1
Dual Super User III
Dual Super User III

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (800)