How to use a SQL Server Stored Proc (with no parameters) as a datasource ?

[Anonymous]

Ran into an issue while trying to use a SQL Server Stored Procedure as a data-source in Power BI desktop. I get the error "Microsoft SQL: Incorrect syntax near the keyword 'EXEC'.". Kind of ironic that an error is encountered with Microsoft's own DataBase product. I have tried both "Direct Query" & "Import" options with the exact same end-result. I am able to EXECute the Stored Proc just fine within SQL Server Management Studio with the exact same userid that I use in Power BI. Appreciate any help

Thanks

Jagannathan Santhanam

[Dharmendharan_L]

Hi All,
Is there any update regarding this issue ?. Im finding the same problem, Stored procedures can not be used in Direct Query but works with Import mode. 
If anyone found a solution regarding this issue, please share your idea to overcome this.
Thanks

Dharmendran

[gluizqueiroz]

Yes, you can use a stored procedure as "datasource" IF the stored procedure returns a table. I have created this simple test and it works:

CREATE TABLE TableTest
(
	NAME VARCHAR(100),
	AGE INT
)

INSERT INTO TableTest
VALUES
('Jonh Doe', 20),
('Tom Wayne', 35),
('Tony Clark', 40)

SELECT * FROM TableTest

CREATE PROCEDURE ProcedureTest
AS 
BEGIN
	SELECT * FROM TableTest
END

EXEC ProcedureTest

If I execute this SQL code on SQL Server Management Studio it will return 3 lines with 2 columns containing the content of TableTest.

If you go to Power BI and go to "Get Data", choose "SQL Server Database" and put the IP, click on bottom button to "Advanced Options" and put EXEC ProcedureTest in the field for "SQL Instruction" it will load and show the return of procedure, if the return is a table, you will see a table. Like the following:

[Anonymous]

Same issue, I have the latest version of PBI Desktop "Version: 2.82.5858.961 64-bit (June 2020)"

[gluizqueiroz]

Could you try "Import" type and use "EXEC procedure" instead "EXECUTE procedure"?
(I use Power BI in Portuguese)

Return:

[jdbuchanan71]

@gluizqueiroz is correct, it only works with import mode, not direct query.

[Anonymous]

Well, if my understanding of import mode is correct, it defeats the whole purpose of a live "Direct Query". I don't want to manually refresh data every time the data changes in the database, which is every minute, every day.

[gluizqueiroz]

So, you can use Direct Query and copy the select statement (that is inside procedure) and paste it on Power BI, instead use "EXEC procedure".

Use SP_HELPTEXT 'YourProcedure' on SQL Server Management Studio > Copy the sql statement between BEGIN and END > Paste it on SQL Statement field on Power BI > Choose Direct Query

It should works, but maybe is necessary make some changes on sql code.

[Anonymous]

I could as well embed SQL in my direct connection, however, it defeats the whole purpose of code encapsulation. If I need to change the code, I don't want to do it in Power BI, it should be done at the SQL Server level.

BTW, I implemented a view using the SQL and it works. This is for sure an enhancement for the Microsoft Power BI developer team

[jdbuchanan71 · Accepted solution]

In SQL can you create a view that calls the stored proc then hit the view from Power BI?

[Anonymous]

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

[jdbuchanan71]

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.

[jondufault]

This is too clever. Thank you so much!!!!!

[Anonymous]

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.