Forum Discussion

gijeet's avatar
gijeet
New Member
10 years ago

Call an SP

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.

Need Help

6 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    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's avatar
      aakgun
      Frequent Visitor
      10 years ago

      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's avatar
        Eric_Zhang
        Microsoft Employee
        10 years ago

        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.