cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Issue with Execute SQL query with formal paramter in Flow

Hi,

 

I am currently having an issue with Executing a SQL query with parameter in Flow.

I have a PowerApp that calls a flow and give  certain paramaters to the Flow.

The goal of the Flow is that it Executes A query an give the result back to PowerApps.

 

I created a flow that takes input from Power Apps.

After that I use a Compose to transform the inputvariable (formatting input date to 'yyy-MM-dd')

After that I want to execute a SQL query with the output of the Compose as a formal parameter.

However it seems that he is not assigning any value to the formal paramater.

 

He is running succesfull, but does not return a value for @datum. He seems not store the date in that variable (it is not a connection issue since he do return the BD_opbrengst value).

Does anyone have an idea what I am doing wrong? Changing the datatype of the variable does not help either.

 

See below the ouput that I get when I test the flow:

 

Invoer
query
declare @datum as char(10); select @datum as datum, BD_opbrengst from dbo.opbrengsten
formalParameters
{
  "datum": "2011-08-15"
}
Query Results
{
  "OutputParameters": {},
  "ResultSets": {
    "Table1": [
      {
        "datum": null,
        "BD_opbrengst": 5000
      }
    ]
  }
}
 

Thanks!

3 REPLIES 3
v-yuazh-msft
Community Support
Community Support

Hi @Anonymous ,

 

Could you please share a screenshot of the configuration your flow?

Could you please explain more details about your issue?

 

Please share more details so we would try to provide a proper workaround for you.

 

Best regards,

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Alice,

 

Below the flow I am currently testing.

So I ask from PowerApps a variable, put that in Compose to transform that variable, and then assign the output of   compose as Formal Paramater to Excecute SQL query.

In picture you see that I give that value to "datum".

Whener I execute the query Select @datum he gives as ouput NULL.

Even when I just initialize a variable in Flow and give it a default value and give it then as a formal paramater to Execute a Sql query, he gives ouptut NULL.  So  whatever I try I do not get a value for the formal parameter returned by the query . He seems not able to assign the value to the formal paramater.

He do return the value from BD_opbrensgt (so there is no connectivity issue).

 

 

powerapp.png

Thanks!

Posting to this old thread just in case others stumble in here....

 

Using the Execute a SQL query (V2) instead of the now deprecated v1, here are some hopefully helpful details.  There are some very NON-intuitive parts so hopefully this helps others.

 

As Mr.Dang mentions in his video in this blog post regarding the v1 of this action (https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/Friday-Functions-Series-SQL-Declare-..., put the name of your sql parameter WITHOUT the typical SQL variable prefix of @  in the "key" tbox of the formalParameters entry line.  Then in the "value" tbox, put in the SQL datatype for that parameter....e.g. INTEGER, DECIMAL(18,2), NVARCHAR(25).

 

Next, in your SQL statement put the SQL parameter WITH the proper @  prefix in the appropriate place(s) in your SQL statement.  You no longer need to include a DECLARE for each parameter.   Strangely, you do NOT need to put delimiters around string-type parameters in the SQL statement. The action takes cares of that when it calls the SQL Server.  E.g. note in this image I have an NVARCHAR(500) parameter but there are no singleticks around the parameter in the statement.

DeeTronSEAM_0-1631986058175.png

 

I did not have to save and close the flow to get the data-entry row for the parameter to show up. It appeared as soon as I filled in the formalParameters line for my parameter and clicked out of the value field.  Once the data-entry row appeared for my parameter, I added my dynamic value (i.e. Response ID).

 

When run, here's the goods (i.e. the raw output):

{
    "statusCode"200,
    "headers": {
        "Pragma""no-cache",
        "Transfer-Encoding""chunked",
        "Vary""Accept-Encoding",
        "x-ms-request-id""070b5221-c20d-4ddd-a889-e8424e9a444b",
        "Strict-Transport-Security""max-age=31536000; includeSubDomains",
        "X-Content-Type-Options""nosniff",
        "X-Frame-Options""DENY",
        "Timing-Allow-Origin""*",
        "x-ms-apihub-cached-response""true",
        "Cache-Control""no-store, no-cache",
        "Date""Sat, 18 Sep 2021 17:56:59 GMT",
        "Content-Type""application/json; charset=utf-8; odata.metadata=minimal",
        "Expires""-1",
        "Content-Length""66"
    },
    "body": {
        "ResultSets": {
            "Table1": [
                {
                    "JDSEntries"3
                }
            ]
        },
        "OutputParameters": {}
    }
}

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,607)