cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
torsten156
Regular Visitor

Flow with result from MSSQL-Stored procedure

Hi,

I use a stored procedure in MSSQL with an Output-Parameter. When I start this SP in SQL-Management Studio I receive an integer value as output. Ok, it works fine.

When I use this SP in Power Apps Flows, the result what i get is NULL. Here is the Outputs:

{
    "statusCode": 200,
    "headers": {
        "Pragma": "no-cache",
        "Transfer-Encoding": "chunked",
        "Vary": "Accept-Encoding",
        "x-ms-request-id": "035bc1ef-23d3-487c-bb58-61ee00130807",
        "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": "Mon, 01 Mar 2021 08:47:42 GMT",
        "Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
        "Expires": "-1",
        "Content-Length": "39"
    },
    "body": {
        "ResultSets": {},
        "OutputParameters": {}
    }
}

 

I think the place of the MSSQL-Result is in the body/ResultSets, correct?  

What can I do here?

 

Thanks

Torsten

2 REPLIES 2
ManishJain
Solution Sage
Solution Sage

HI @torsten156 ,

 

In your Stored procedure return by putting select statement.

 

Thanks

Hi @ManishJain 

 

sorry for my late reply...

I've changed now the SP with a select statement. Here is the Code:

 

ALTER PROCEDURE [dbo].[Check_existing_WH] 
	-- Add the parameters for the stored procedure here
	@pCompany int = 0,
	@pSite int = 0,
	@pMonth varchar(10) = 'Jan',
	@pYear int = 0,
	@return int OUT

	
AS
BEGIN

	SET NOCOUNT ON;

	if not exists (SELECT WH_Employee from [dbo].[tbl_Working_Hours] where Company = @pCompany AND Site = @pSite AND Month = @pMonth AND Year = @pYear)
		Begin
			select @return = 0
		End
	Else
		Begin
			select @return = 1
		End
END

In MSSQL I get a result (1 or 0). This procedure works fine.

 

 When I use this SP in Power Apps, I don't get a return value. In the body-area I get this:

 

{
  "ResultSets": {},
  "OutputParameters": {}
}

 

What is my problem here???

 

Regards

Torsten

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (12,349)