Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
ebjm
Regular Visitor

How to pass a parameter to a MySQL stored procedure from powerBI

Hi - I am using powerBI to connect to a MySQL server. We can connect fine and we can receive data fine - I am calling a stored procedure from powerBI using the following command;
CALL my_sp();


My problem is that when I try to pass a parameter to the stored procedure like this;
SET @p0 = 'xyz';

CALL my_sp(@p0);

 

Power BI returns a message saying I should use an unencrypted connection. When I do it then gives the following error: "MySQL: Fatal error encountered during command execution."

 

So my question is - how can I pass a parameter to a MySQL stored procedure from powerBI?

I'd be grateful for any pointers / advice. Thank you.

9 REPLIES 9
Anonymous
Not applicable

Hey @ebjm

 

Hopefully this can help you out. I wrote this reply on another thread:

 

"It turns out that this is actually possible in Power BI. I've created a walkthrough video on how to do this here.

 

The basic gist is to add an R visual that takes in your slicer selection as its value. There is an R package called RODBC that can run SQL code against your database. You can then pass your slicer value into the SQl query using some string interpolation or the "paste" function. Let me know how this works for you."

 

 

 You will need to change the connection type from SQL Server to MySQL.

 

Hope this helps,

Parker

 

Thanks but I am puzzled. Why can I pass the following from Power BI to my MySQL server?
CALL my_sp();
but I am not allowed to pass
SET @p0 = 'xyz';
CALL my_sp(@p0);

Anonymous
Not applicable

How are you passing it?

Capture.PNGI pass it through the 'SQL statement' text field after 'Get Data'.

Anonymous
Not applicable

I'm not too familiar with MySQL but when connecting to SQL Server, Power BI will wrap the entire command in a SELECT. 

 

That leads me to believe that 

SELECT
*
FROM (CALL my_sp();
)

 will work but the following won't:

SELECT
   *
FROM
   (SET @p0 = 'xyz';
   CALL my_sp(@p0);
   )

Maybe that's the reason for your troubles. But like I said in an earlier post, take a look at my video and you can get around your issue if your situation fits. It involves using an R visual to run your SP instead of the Get Data function of Power BI.

 

Hope this helps,

Parker

So you can use the R code will pass the date parameter in your Stored Procedure and this will work with more than one parameters too?

Anonymous
Not applicable

Yep, you can pass as many fields into the R visual as you need. You can then reference all of them in your SQL code embedded in your R code

I am going to try this tonight, does your video detail all the necessary information? Also for the RODBC connector, do we need to download that on SSMS?

Anonymous
Not applicable

The only difference between my video and your situation is that you will be passing in a Power BI parameter. To do this, you will need to create the parameter and enable load so that you can then pass it into the R Visual. As for RODBC, that's an R package that you will need to install. Other than that, you should be good to go. Feel free to reach out with questions.

 

Edit: Reading back through your initial post, not sure if you are passing a Power BI parameter. You will be able to pass any column or Power BI parameter using this method.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.