cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omi18
Post Patron
Post Patron

Execute Stored Procedure Error

Hello,

 

I have stored the SQL procedure set to run on the Button click property. And in the stored procedure, Except ID column is null. But whenever I try to run the procedure it gives me an error "The Method run has an invalid parameter". 

 

And if I try to run the procedure from the database end it works fine. 

 

Any suggestions? 

 

Thanks,

Omi

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

I don't have anything quite as extensive as yours, but maybe this will help.  The following is what is on the onSelect to run the flow. It builds a record with all the parameters and then sends it as JSON to the flow.

UpdateContext(
    {
        params: {
            Customer: Concatenate(
                txtCustomer.Text,
                "%"
            ),
            EndDate: Text(
                dteEndDate.SelectedDate,
                "[$-en-US]yyyy-mm-dd"
            ),
            MaxValue: 50000,
            MinValue: 40000,
            StartDate: Text(
                dteStartDate.SelectedDate,
                "[$-en-US]yyyy-mm-dd"
            )
        }
    }
);
ClearCollect(
    returns,
    StoredProcJSON.Run(
        JSON(
            params,
            JSONFormat.IndentFour
        )
    )
)

And here is the flow.  I Parse the parameter coming in and use those values in the stored procedure.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
Pstork1
Dual Super User III
Dual Super User III

You can't run a stored procedure using Power Apps.  You can only run it using Power Automate. Its one of the limitations of the SQL connector in Power Apps. Check the "Known Issues and Limitations" here:
SQL Server - Connectors | Microsoft Docs



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
omi18
Post Patron
Post Patron

Hi @Pstork1 ,

 

Yes, I'm running the flow (Automate) from powerapps. 

 

Thanks,

Omi

Pstork1
Dual Super User III
Dual Super User III

Is the ID parameter one of the required parameters for the stored procedure?  Have you double checked the data types for all the values you are using for parameters?  Can you provide a screenshot of the Execute Stored Procedure V2 action you are using in the flow?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
omi18
Post Patron
Post Patron

I have below screen;

 

omi18_0-1615222913918.png

Out of that only ID, is mandatory. And is set up the same way in a stored procedure as well. This procedure only works if I fill in all the values. Otherwise gives me the same error for empty fields. 

 

Thanks,

Omi

Hi @Pstork1 ,

 

Below is my Flow for the procedure.  Yes, I did check the datatypes. 

omi18_0-1615223556438.png

Thanks,

Omi

Pstork1
Dual Super User III
Dual Super User III

SQL stored procedures allow for parameters to be null, but Power Automate doesn't have that concept.  So although the stored procedure doesn't require the parameter the Run command to invoke the flow will require a value for each of the "Ask in PowerApps" prompts you've created.  Its a bit more work, but with this many fields to pass I usually find it easier to pass a JSON object with the values in it rather than individual "Ask in Power Apps" values.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

I see, can you share an example/reference related to my scenario? 

 

It would be great. 

 

Thanks,

Omi

Pstork1
Dual Super User III
Dual Super User III

You mean an example of passing the parameters as a JSON object?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
omi18
Post Patron
Post Patron

Hi @Pstork1 ,

 

Yes.

Helpful resources

Announcements
PA 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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (89,818)