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:
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"):
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:
Flow Execution results:
Failure Summary:
Error code screenshot:
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" }
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.
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!
User | Count |
---|---|
6 | |
6 | |
5 | |
3 | |
1 |