On-Prem SQL Stored Procedure only returning as action

[EJRebirth]

Hello,

I've posted this on another community forum in error (the Power Apps community) before realising my issue was with Microsoft Flow.

We're currently in the process of testing out Microsoft Flows to use within PowerApps, and we've managed to connect to our On-Premise SQL 2014 server succesfuly.

We're trying to run a variety of on-premise Scalar Functions and we are met with instant failure with a typical error message being:

We cannot convert a value of type Table to type Action. inner exception: We cannot convert a value of type Table to type Action

or

We cannot convert the value true to type Action.

Is it even possible to have data returned, or is this a limitation of using an on-premise gateway currently? I simply do not see a way to have data returned properly. Can anyone advise?

I have attached a picture for completeness. This function ascertains if a date falls on a working day which should return true or false. As you can see, it returns True, but the flow wants to return as an Action.

Is there anyone that can advise as to what is going wrong, every function is trying to convert to an action, when I just want it to return as the datatype it's intended for.

[v-yamao-msft]

Hi @EJRebirth,

Please make sure your stored procedure is properly configured.

I made a simple flow for testing with the action Execute stored procedure, and the value can be returned properly. Please check the following screenshot.

Known issues and limitations about SQL Server, please check it at here:

https://docs.microsoft.com/en-us/connectors/sql/

Best regards,

Mabel Mao

[EJRebirth]

Hi Mabel Mao,

I've attempted to create a fresh function which only selects a value from a table, and the error is still the same. Could you provide me with your working example to compare differences.

[v-yamao-msft]

Hi @EJRebirth,

Please take this blog for a reference:

How To Create A Stored Procedure In SQL Server Management Studio

The stored procedure I made on my side is rather simple, please check the following:

Hope this could be a reference for you.

Best regards,

Mabel Mao

[EJRebirth]

Ok, so I've discovered that Store Procedures work fine regardless, but Scalar-valued Functions do not, despite being selectable from the list. Maybe this is where the confusion has come in.

I can run a stored procedure, that calls a scalar-valued function, and this works, but running a scalar function direct always fails with the same message regarding unable to convert type to action.

Are Scalar-Valued Functions still a work in progress, or in preview, or do they simply not work despite being avaiable to select?

I have included the test for you to try yourself.

CREATE FUNCTION [dbo].[testScalar]
(
	-- Add the parameters for the function here
	@a int = 1,
	@b int = 2
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @c int

	-- Add the T-SQL statements to compute the return value here
	SELECT @c = (@a+@b)

	-- Return the result of the function
	RETURN @c

END
GO

[EJRebirth]

Was wondering if you've had a chance to try my test out, and whether there is indeed an issue with what I'm doing, or if there is an issue with Flow in general?

[Anonymous]

---

@EJRebirth wrote:

Ok, so I've discovered that Store Procedures work fine regardless, but Scalar-valued Functions do not, despite being selectable from the list. Maybe this is where the confusion has come in.

I can run a stored procedure, that calls a scalar-valued function, and this works, but running a scalar function direct always fails with the same message regarding unable to convert type to action.

---

@EJRebirth, We have the same issue with a table value function.  I can see it in the list of stored procs, but cannot run it without the same error you got.  Did you get this to work without calling a stored proc that calls a function?

[TechnoKitty]

@EJRebirth and @Anonymous 
Try this: https://cloudminded.blog/2021/02/02/getting-data-from-a-function-in-an-on-prem-sql-server-via-gateway-in-power-automate/