PowerApps + Flow: SQL Server 2012 Stored Procedure with Parameters

[mrb783]

Hello,

(Had this in the Connecting to Data forum, but figured this was a more appropriate forum). 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"):

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"
}

[manuelstgomes]

Hi @mrb783 

Hi

I just stumbled on your request. You've been waiting a while for a reply.

Were you able to solve it? Can you post the solution so that we can all learn from it?

If you still need help, please let me know what changed.

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