cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrb783
Level: Powered On

PowerApps -> Flow -> SQL Server Stored Procedure (Parameters do not pass correctly using Flow)

Hello,

 

I'm trying to use PowerApps to pass parameters to Flow for a SQL server stored procedure execution. However, I'm running into an issue with how Flow is passing the parameters (best guess at this point honestly, but only thing I can come up with). Here's what I know:

 

  • The stored procedure takes in quite a few parameters (~20 of them; all varchar value types of varying sizes).
  • I've tested the SP extensively outside of Flow via manual test case scenarios and have confirmed that it is functioning appropriately.
  • When viewing the issues, the same values that are passed to Flow are used separately in manual test case scenarios outside of Flow and they work fine.
  • I get a Code 400 issue (Bad Request) indicating that my SQL statements are invalid (which implies that the parameter values are not being passed correctly).
  • (stretch fact; uncertain if relevant) I've even went so far as to alter the SP to allow for "dummy text" inputs (when the parameter value is "~") and then convert that to null values, in the hope that maybe it was an issue with passing blank values. It wasn't.
  • I have deleted/recreated the Flow several times and it does not change the results.
  • By all accounts, the values that are visible within the Flow execution attempt history for the failed run show exactly what I would expect to function correctly (all are text entries and exactly what I expect). Using the same test scenarios outside of Flow work flawlessly.
  • The SP in question uses the parameters to build a dynamically created SELECT query. Again, this works fine when used outside of Flow.
  • I am able to connect to and execute other stored procedures (on the same database in the same PowerApps application) without any parameters just fine with no errors.

Below are some snapshots of my button.select and Flow details (varFilter* values are boolean variables within PowerApps that are based on checkboxes, but only get updated when the filtering selections are complete and 'applied' within the app menu):

 Note: "FlowName" is generic and replaced in this example. Thanks in advance!

 

FlowName.Run(
    If(varFilterAsOf,Text(dtpHomeAsOfDate.SelectedDate,ShortDate),Text(Today(),ShortDate)),
    If(varFilterWorkerStatus,drpHomeWorkerStatus.Selected.Value,"~"),
    If(varFilterWorkerType,drpHomeWorkerType.Selected.Value,"~"),
    If(varFilterEmployeeType,drpHomeEmployeeType.Selected.Value,"~"),
    If(varFilterContWorkerType,drpHomeContWorkerType.Selected.Value,"~"),
    If(varFilterSOALevel && "0" in lstHomeSOALevel.SelectedItems.Value,"0","~"),
    If(varFilterSOALevel && "1" in lstHomeSOALevel.SelectedItems.Value,"1","~"),
    If(varFilterSOALevel && "2" in lstHomeSOALevel.SelectedItems.Value,"2","~"),
    If(varFilterSOALevel && "3" in lstHomeSOALevel.SelectedItems.Value,"3","~"),
    If(varFilterSOALevel && "4" in lstHomeSOALevel.SelectedItems.Value,"4","~"),
    If(varFilterSOALevel && "5" in lstHomeSOALevel.SelectedItems.Value,"5","~"),
    If(varFilterSOALevel && "6" in lstHomeSOALevel.SelectedItems.Value,"6","~"),
    If(varFilterSOALevel && "7" in lstHomeSOALevel.SelectedItems.Value,"7","~"),
    If(varFilterSOALevel && "8" in lstHomeSOALevel.SelectedItems.Value,"8","~"),
    If(varFilterEmployeeEnum,txtHomeEmployeeENum.Text,"~"),
    If(varFilterSupervisorEnum,txtHomeSupervisorENum.Text,"~"),
    If(varFilterTermDtFrom,Text(dtpHomeTermDtFrom.SelectedDate,ShortDate),"~"),
    If(varFilterTermDtTo,Text(dtpHomeTermDtTo.SelectedDate,ShortDate),"~"),
    If(varFilterOrgSearchTerms,txtHomeOrgSearchTerms.Text,"~"),
    If(varFilterJobSearchTerms,txtHomeJobSearchTerms.Text,"~"),
    User().Email
)

 

Flow (all fields were populated with "Ask in PowerApps"):

flow.png

 

Example SP execution call (that works):

EXEC [dbo].[SPNAME] '4/17/2018', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~'

This produces the same results as the above (I did the "~" characters due to the potential for null values to cause issues...the SP logic takes care of the dummy characters):

EXEC [dbo].[SPNAME] '4/17/2018', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''

Proof of non-issues during independent SP call:

SPresults.png

 

Flow Execution results:flowresults1.png

 

Failure Summary:failuresummary.PNG

 

Error code screenshot:errorscreen.PNG

 

Error code text:

{
  "status": 400,
  "message": "Microsoft SQL: Incorrect syntax near 'WHERE'.\r\n     inner exception: Microsoft SQL: Incorrect syntax near 'WHERE'.\r\nclientRequestId: 76dd8643-d248-42c5-8093-19637b9267da",
  "source": "sqlconnectionprovider-westeurope.am2-ase-001.p.azurewebsites.net"
}

 

1 REPLY 1
mrb783
Level: Powered On

Re: PowerApps -> Flow -> SQL Server Stored Procedure (Parameters do not pass correctly using F

Update: Fixed. Solution: it was my "definitely tested and working Stored Procedure" using one too many of the parameters to test when building my WHERE clause (ended up with "WHERE ()" in my SQL statement). Took a while to figure out what was going on.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,715)