Execute SQL Server stored procedure in Direct Query mode without OPENROWSET

[alekdavis]

We need to implement complex reports against a cloud hosted database server in direct query mode. The goal is to use parameters to limit the amount of data returned from the server and make it flexible (e.g. allow user to use different sorting options, which when combined with result set limit must be done on the server side). The obvious choice would be to call a stored procedure, but based on suggestions from other posts on the topic, the only options for calling a stored procedure form Power BI is via OPENROWSET. The problem is that it requires changing server configuration (for the 'show advanced options' and 'Ad Hoc Distributed Queries' settings) and we do not have this permission. Another suggestion I found was to use user defined functions instead of stored procedure. The problem here is that user defined functions do not support dynamic SQL (EXEC or sp_executesql) and we need to use these because there is no other way to build our queries (for example, we need to use comma-separated lists of values passed by the user in the WHERE clause and I do not think you can do this in any other way). Is there anything else we can do?

[Shalb1]

@alekdavis did you find a solution to this? I have a similar issue and would like to use stored procedure with direct query, and not use openrowset. Please let me know if you found any workarounds.

Thanks!

[alekdavis]

Unfortunately, no. I am still flabbergasted by the fact that PowerBI doe snot support stored procs.

[ndj2212]

Hi, did you ever find a solution to this? I'm facing exactly the same situation - need to use a SQL Server SP with dynamically changed M query parameters, and cannot use OPENROWSET 

[lbendlin]

Have you ruled out query folding as an option?

Parameterizing Value.NativeQuery For Query Folding | Sandeep Pawar (pawarbi.github.io)

[alekdavis]

Thanks, but I'm not sure if this would help in our case. I'm totally new to Power BI, so maybe I miss something, but the tip seems to address the implementation using Power BI query language and the goal we have is to use a standard stored procedure (we have other consumers of the same report, not just Power PBI, so it would make more sense to implement all logic on the database side in the stored and thyen have them all call the stored proc, instead of having every tool use their proprietary language where we'd need to redefine the logic). If the tip was supposed to address passing parameters to UDF  as a string and them using OPENJSON to convert them into a list, I need to try it out, but there are other limitations that may make UDFs impractical (e.g. we may need to use a temp table with indexes, which is not an option for UDF, and there are other limitations).