Execute SQL Query with Parameters

[tdupton]

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

[bpremji]

I am trying to something similar and followed this video:

https://powerusers.microsoft.com/t5/Community-Video-Gallery/Friday-Functions-Series-SQL-Declare-Form...

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?

[dhamacher]

Hi,

Has this issue been resolved yet?

Kind Regards,

Daniel

[rdumont]

anyone get this to work?

[rrunnels]

Bump... 

[DeeTronSEAM]

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.

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

    }

}

[Middelwareasv]

Works. thx

but only in old editor not in new:(