Forum Discussion
Questions on adding sprocs that contain parameters.
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:
- The article states that this only works for Import Mode -- is there a workaround for this serious limitation?
- 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.
- 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!
3 Replies
Hi Anonymous ,
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,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
SourceThis 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
SQLSourceI'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?
