Questions on adding sprocs that contain parameters.

[chrisrdba]

Greetings. Using this awesome article, I was able to figure out how to add sprocs that accept parameters to my first report. However, I still have a couple questions:

1. The article states that this only works for Import Mode -- is there a workaround for this serious limitation?
2. When I go to Advancd Editor and modify my code to accept a parameter, then go back to the main Power BI Desktop, the Fields have vanished. I put the old code back in to not use a parameter, and they come back. Obviously this stops me from then building a report. I can reproduce this at will and will post the code below.
3. Even though I've set my paramter as not required, is is in fact required to exec the sproc -- at least in the preview pane of Advanced Editor. Will this no longer be the case once I'm able to exec from the main page (currently prohibtd by # 2)?

--This code will show me the fields

let
Source = Sql.Database("myInstance", "myDB", [Query="exec usp_rpt_ServersDatabases"])
in
Source

--This code won't show me the fields

let
SQLSource = (dbInstance_name as text) =>

let
Source = Sql.Database("myInstance", "myDB", [Query="exec usp_rpt_ServersDatabases '"& (dbInstance_name)&"' #(lf)#(lf)"])
in
Source
in
SQLSource

Thanks!

[v-lid-msft]

Hi @chrisrdba ,

For first question, if you mean using directquery with Store procedure please refer to the post below that makes use of OPENROWSET : https://community.powerbi.com/t5/Desktop/Calling-SQL-stored-procedure-with-Direct-Query/td-p/224831

For second question, please try to invoke the new code/function, it will generate a query with table (Step 4 in the blog)

Sorry for that we can not understand the last question clearly , do you mean how to access the Store Procedure without using sql statement in advanced editor?

Best regards,

[chrisrdba]

I'm attempting to rewrite # 1 w OPENQUERY, I can make it go as long as I'm not attempting to pass in a paramter:

This works:

let
Source = Sql.Database("myServer", "myDB", [Query="select * from openquery ([myServer], 'exec myDB.dbo.usp_rpt_ServersDatabases');"])
in
Source

This doesn't:

let
SQLSource = (dbInstance_name as text) =>

let
Source = Sql.Database("myServer", "myDB", [Query="select * from openquery ([myServer], 'exec myDB.dbo.usp_rpt_ServersDatabases '"& (dbInstance_name) &"'"])
in
Source
in
SQLSource

I've attempted several variations of this, and it always yells about one thing or another. Is this possible and I'm jsut doing it wrong, or is it not possible?

[chrisrdba]

Regaring # 2..

I can in fact invoke a new function which I can then Publish to my workspace. However, I then am stuck with whatever data was captured when I fed in the paramter back in Power BI Desktop -- I cannot input a new paramter and get different data. 

What am I missing here?