Help - SQL execute store procedure action in Power Automate does not return output value

[Moonligh]

I created a flow that triggers when a SharePoint item in a list is modified. This flow calls data from an 'Execute store procedure' action. Based on the result, the data is inserted into a table. The stored procedure receives two parameters and returns one. If I enter the parameters manually, it works correctly. However, if I pass the parameters as triggerBody()?[‘EmailAddress’], it doesn't work. I also tried passing the values to a variable and then passing the variable as a parameter, but this method doesn't work either because the output parameters are empty. For the purpose of this example, I modified the SQL stored procedure to return 2 in the output parameter, regardless of the input parameters.

Can anybody help with this issue please? 

[abm]

Hi @Moonligh 

Did you tested with same email value 'bb' and ADID 'bb' manually and it did worked? Could you show me the previous steps of the flow too?

Thanks

[Moonligh]

Sorry, entering the data manually doesn't return any value neither. The stored procedure works fine when run in SSMS. There should be an output parameter DataExists=2

[abm]

Hi @Moonligh 

So in SSMS when you execute the Stored Procedure with values rrr it did worked? 

[Moonligh]

Yes, it does 

[Inogic]

Hi

I checked the same scenario and I am getting the output parameter.

Could you please share the stored procedure if possible? So we can try to check this further.

Hope this helps.

Thanks!

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com 
Service: https://www.inogic.com/services/ 
Tips and Tricks: https://www.inogic.com/blog/ 

[Moonligh]

USE [IntegSupport]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [BC].[ValidateSignIn]

(

    @Email     NVARCHAR(200), 

    @ADID      NVARCHAR(36),  

    @Emailout  NVARCHAR(200) OUTPUT,

    @ADIDout   NVARCHAR(36) OUTPUT,

    @DataExist INT OUTPUT

)

AS

BEGIN

    SET @DataExist = (

        SELECT CASE 

            WHEN EXISTS (SELECT 1 FROM [BC].[ID_Lookup] WHERE Email = @Email AND ADID = @ADID) THEN 1 -- Data already exists

            ELSE 0 

        END

    );

    IF @DataExist = 0

    BEGIN

        SET @DataExist = (

            SELECT CASE 

                WHEN EXISTS (SELECT 1 FROM [BC].[ID_Lookup] WHERE Email = @Email) THEN 3 --Email address with different ADID

                ELSE 0 

            END

        );

IF @DataExist = 0

  SET @DataExist = (

            SELECT CASE 

              WHEN EXISTS (SELECT 1 FROM [BC].[ID_Lookup] WHERE ADID = @ADID) THEN 4 --ADID already exists with different Email address

            ELSE 0 

            END

);

    END

    IF @DataExist = 3

    BEGIN

        -- If only email exists, retrieve it

        SELECT @Emailout = Email

        FROM [BC].[ID_Lookup]

        WHERE Email = @Email;

    END

    ELSE IF @DataExist = 4

    BEGIN

        -- If only ADID exists, set the output

        SET @ADIDout = @ADID;

    END

END

GO