Call an SP

[gijeet]

As a developer I spent years learning SQL and all it’s power – creating queries, CTEs, correlated subqueries, stored procedures with parameters, learning all the join conditions and filter conditions in the code to return a result set.  Why do I want to do all this manually thru an interface now in Power BI Desktop by getting data, manually creating relationships, manually merging data,  etc. ? We all know once you create code scripts it’s much faster and easier to manage than thru an interface. So ss there a way to call a stored procedure and pass parameters from BI Desktop?  Thanks.

[Eric_Zhang]

Hi gijeet,

I've leant SQL for years as well and be glad to meet someone who has the similar experience 🙂 . Through an interface is also not prefered for me, however I think it might be the savior for the ones who have no any SQL or coding experience.

Regarding your question, calling stored produres with parameters, I've made some research and found some tricks.

e.g. In SQL Server, supposing there exists a stored proce in testdb of testserver.

CREATE PROC p4test @PARAM VARCHAR(200)
AS
BEGIN
SELECT @PARAM CONTENT
END

1. Click "Edit Queries" in the "Home" tab, a "Query Editor" window pops up.
2. Click "New Source" ->"SQL Server" and fill the "server","database","Sql Statement" with "testServer","testdb","exec p4test 'goodday'", click ok.
3. Click on that query on the left panel and click "Advanced Editor" in "Home" tab, and paste the below code

let 
    SQLSource = (param as text) => 
let
    Source = Sql.Database("testserver", "testdb", [Query="exec p4test '"&param&"'"])
in
    Source
in
    SQLSource 

Then you can click "invoke" button and type a parameter value.

That's all what I've found so far.

[aakgun]

Hi @Eric_Zhang,

We could try your steps for using the Stored Procedures with parameters. We could pass the the arguments to the SP but, after each parameters sbmit ans query run, we have gor new dataset result (table).

so we could not use this data in our report as data source.

Is there any option or way to use this SP call with parameters, with refresh of the remaining dataset ?

Any your help or comment is welcome.

Thank you, best regards.

[Eric_Zhang]

@aakgun

What do you mean "refresh of the remaining dataset", if you dataset is the execution result of an SP, when the passed parameters change, the result set changes accordingly.

[aakgun]

Hello @Eric_Zhang,

In Power BI,when we invoke the new Parameter, It executes the SP with parameter but a new dataset table is listed in the Power BI.

I expect that I will have only one data table, (dataset) and SP Execution will  refresh its data.

[aakgun]

 When I checked the steps, I missed applying the query change to the "Invoked Function" in the daset list. I have applied my changes to the Query 1 function, then it resulted every time a new dataset.

As a result, using the Invoked Function dataset is OK for calling Stored Procedure and That is great!!.

At this point , Is there any way of entering parameter data to the query, without using "Edit Queries" details.

End Users need to enter their parameter information or select the requested data in the report screen and refresh the data withou using the "Edit Queries" details.

Thanks for your kind comment ans support again.

[Eric_Zhang]

---

At this point , Is there any way of entering parameter data to the query, without using "Edit Queries" details.

End Users need to enter their parameter information or select the requested data in the report screen and refresh the data withou using the "Edit Queries" details.

Thanks for your kind comment ans support again.

---

AFAIK, there's no way. The demo I showed is just to indicate that Power BI has that capability. However it is not a good practice in my opinion.

Usually a report is developed in Power BI desktop and then published to Power BI Service. Refresh can be scheduled via a gateway. So instead of entering parameter, can all data be imported or in a DirectQuery(stored procedure is not supported in this mode based on my test) and filtered via a slicer?