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

Question

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.

anonymous · Answer

Hey&nbsp;ebjm&nbsp;Hopefully this can help you out. I wrote this reply on another thread:&nbsp;"It turns out that this is actually possible in Power BI. I've created a walkthrough video on how to do this&nbsp;here.&nbsp;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."&nbsp;&nbsp;&nbsp;You will need to change the connection type from SQL Server to MySQL.&nbsp;Hope this helps,Parker&nbsp;

nirvana_moksh · Answer

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 · Answer

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

nirvana_moksh · Answer

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 · Answer

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.&nbsp;Edit:&nbsp;Reading back through your initial post, not&nbsp;sure if you are passing a Power BI parameter. You will be able to pass any column or Power BI parameter using this method.

Forum Discussion

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

9 Replies

Related content

Passing PowerBI Parameter To A Stored Procedure

Stored Procedure to Manipulate Tables

Data compression Procedure

Passing Date Parameters to a Stored Procedure

Errror while fetching data from Snowflake stored procedure in powerbi