cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Frequent Visitor

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
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

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

Users online (5,205)