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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,142)