Power BI DirectQuery with Parameterized Stored Procedure
Issue: You have a parameterized SQL Stored procedure that you want to call in DirectQuery mode. You attempt to use “exec sp parameter” syntax in the Power BI Desktop which works in the Query Editor but it fails to execute due to syntax error when you apply changes.
Workaround: Power BI uses the “select * from exec sp” syntax which doesn’t work. However, if you switch to OpenRowset (you’d need to enable ad hoc distribution queries on the database first), it will work. Other possible workarounds that would probably work is inserting the stored procedure results into a temp table and then select from the table, or wrapping the stored procedure with user-defined table function.
I attach a sample that demonstrates the OpenRowset approach. Download the Stored Procedure Direct Query.zip file (link provided at the end of this post) and rename the from *.zip to *.pbix.
Here is the query text:
= Sql.Database(“elite2”, “AdventureWorks2012″, [Query=”SELECT * FROM #(lf)OPENROWSET(‘SQLNCLI’,’trusted_connection=yes’, ‘exec AdventureWorks2012..uspGetAddress ”” & @City & “”’)”, CreateNavigationProperties=false])
I went one step further and parameterized the stored procedure parameter so that your users can set it conveniently using Edit Queries -> Edit Parameters.
