Forum Discussion
How to use a SQL Server Stored Proc (with no parameters) as a datasource ?
In SQL can you create a view that calls the stored proc then hit the view from Power BI?
In SQL can you create a view that calls the stored proc then hit the view from Power BI?
I don't think a VIEW can execute a stored procedure within itself. I have tried several types of commands and encountered compilation errors. BTW, my original post seems like an area of improvement for Power BI development team
I found the following that might help.
https://intellipaat.com/community/3763/how-to-use-sql-server-stored-procedures-in-microsoft-powerbi
In order to execute stored procedure in Power BI:
-
Go to SQL Server “right-click” on stored procedure and select “Execute”. After execution of your cod, a new query window opens up which was responsible for execution. Copy that Query.
-
Go to Power BI Query Editor, Click on New Source and select SQL Server. After giving the server and database, click on "Advanced Options", paste the query in the "SQL Statement" . Navigate using full hierarchy and click ok.
-
You will see data for the parameters you passed in Stored procedures only.
Remember that this works on "Import Query" option.
Already tried that, I only see Tables and views. I don't see Stored Procedures, although the specific Stored Proc was GRANted the correct rights.
-
Correct, you cannot call a sotred procedure from a view. What you can do is have the stored procedure write to a table and then schedule a SQL Agent job to run the stored procedure at your desired interval and then just use that table as your PBI data source.
