Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : E6VhKU9RQQpgCTIkaUNYES
Power Platform Community / Forums / Power Automate - Using Connectors / Execute SQL Query with...
Power Automate - Using Connectors
Unanswered

Execute SQL Query with Parameters

Like (0) ShareShare
ReportReport
Posted on 5 Feb 2019 14:56:12 by tdupton 57

I'm trying to execute a query and return results to PA.  I can't get the query to run successfully with a parameter...although I can hardcode the parameter and the query works fine.  It picks up the parameter input but never returns a ResultSet.

Declare @UnitNumber int
SELECT AVG(SOSTotalTime) AS AverageSOS, DayPart 
FROM dbo.vw_SOSbyOrder 
Where UnitNumber=@UnitNumber
GROUP BY DayPart

Capture.PNG

  • Middelwareasv Profile Picture
    Middelwareasv 5 on 19 Apr 2024 at 14:44:33
    Re: Execute SQL Query with Parameters

    Works. thx

    but only in old editor not in new:(

  • DeeTronSEAM Profile Picture
    DeeTronSEAM 396 on 18 Sep 2021 at 18:03:54
    Re: Execute SQL Query with Parameters

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

     

    Using the Execute a SQL query (V2) does work for me.  There are some very NON-intuitive parts so hopefully this helps others.

     

    As Mr.Dang mentions in his video regarding the v1 of this action, 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": {}
        }
    }

     

  • rrunnels Profile Picture
    rrunnels 5 on 18 Nov 2020 at 14:57:45
    Re: Execute SQL Query with Parameters

    Bump... 

  • rdumont Profile Picture
    rdumont 2 on 01 May 2020 at 15:49:53
    Re: Execute SQL Query with Parameters

    anyone get this to work?

  • dhamacher Profile Picture
    dhamacher 2 on 31 May 2019 at 17:49:10
    Re: Execute SQL Query with Parameters

    Hi,

     

    Has this issue been resolved yet?

     

     

    Kind Regards,

    Daniel

  • BharatPremji Profile Picture
    BharatPremji 8 on 15 Feb 2019 at 04:02:02
    Re: Execute SQL Query with Parameters

    I am trying to something similar and followed this video:

     

    https://powerusers.microsoft.com/t5/Community-Video-Gallery/Friday-Functions-Series-SQL-Declare-Formal-Parameters-Run-Power/td-p/164292

     

    2 minutes and 20 seconds into the video, he saves the flow and refreshes the page, which makes the variable appear. 

     

    I cannot get this to appear, which I think maybe the issue we are both facing.  

     

    Has anyone else faced this issue?

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,369 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans