Calling Stored Procedure for Big Query as Power BI Data Source

[arcticera771]

Hello!

I have been struggling to get a Stored Procedure I created in BigQuery to load in as a dataset in PBI. 

I have created the required ODBC connections, and I am able to run simple direct SQL statements like "SELECT * FROM '[projectid].[server].[storedprocedurename]." But when I try to "CALL '[projectid].[server].[storedprocedurename]'();" I keep getting syntax errors that I cannot verify (because of M?):

I have read elsewhere that it has something to do with Enable Folding? But I've tried this from another forum post and it gives another syntax error: https://community.fabric.microsoft.com/t5/Desktop/Call-Stored-Procedure-from-BigQuery/td-p/1698474

What am I missing here? Or, is this just not possible?

I have also tried using the stored procedure as a SQL Statement directly but still run into syntax errors.

Any help is greatly appreciated!

[v-pagayam-msft]

Hi @arcticera771 ,
I wanted to check and see if you had a chance to review our previous message or Please let me know if everything is sorted or if you need any further assistance.If it helps,consider accepting it as solution.
Thank you.

[v-pagayam-msft]

Hi @arcticera771 ,

Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[v-pagayam-msft]

Hi @arcticera771 ,
I wanted to follow up on our previous suggestions regarding the issue you are facing currently. We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.

[mh2587]

Power BI does support executing stored procedures in DirectQuery mode, but there are certain limitations. For instance, stored procedures that return multiple result sets or include output parameters are not supported.

[arcticera771]

I have not been trying to use DirectQuery mode: I have just been trying to connect to the datasource via Get Data > Google BigQuery > Advanced Options > SQL Statement.

After successful connection, I am able to select Import.

My Stored Procedure as is currently creates a number of temp tables; would this be an issue?

[mh2587 · Accepted solution]

Yes, you can use stored procedures in Power BI Import mode by entering a native SQL query (e.g., EXEC MyProcedure), but only if the procedure returns a single result set and has no output parameters. For more stability, it's better to use a SQL view instead.

[arcticera771]

Thank you! 

At the moment, I converted the SQL query to use tables instead of temp tables, then I use a query in Power BI to pull from there. I'm hoping to convert that to a view instead.

Thank you all for your timely feedback!