Forum Discussion

MRZ's avatar
MRZ
Frequent Visitor
10 years ago
Solved

parameters to SP or Sql query

There is probably a simple answer but after looking all over for an answer and trying various methods, the right code still escapes me.  The scenario is I have a stored procedure that uses two input ...
  • MRZ's avatar
    MRZ
    10 years ago

    Thank you so much. Will give this a go as soon as possible.

  • MRZ's avatar
    MRZ
    10 years ago

    By the way, this work fine. Thanks so much. Now I need to find out how to update the parameters in PowerBI.com so that i can refresh the data as needed. Thanks again.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi MRZ,

I made a test to call stored procedure with parameters in Power BI Desktop. You can review the following example to apply it to your scenario.

In SQL Server, I create a procedure named p4test in test database of v-kaxion2015tes\sql2016tabular server.

Create PROC p4test @sdate date,@edate date
AS
BEGIN
SELECT @sdate StartDate, @edate Enddate
END


1. In Query Editor of Power BI Desktop, click "New source"> "SQL Server", enter server name, database name and statement "exec p4test '8/11/2016', '8/12/2016'", click ok.

2. Right click on that query on the left panel and select Advanced Editor, and paste the below code.

let
    SQLSource = (param1 as date, param2 as date) =>
let  
    Source = Sql.Database("v-kaxion2015tes\sql2016tabular", "test", [Query="exec p4test '"& Date.ToText(param1) & "','" & Date.ToText(param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
    Source
in
    SQLSource

3.Click “Invoke” button and enter parameter values as shown in the following screenshot.



Thanks,
Lydia Zhang

SvenTexas's avatar
SvenTexas
Frequent Visitor
9 years ago

Thanks Lydia, this worked perfectly.  Would you have any idea how to just pass a textual value, instead of date.  So Text.ToText or similar? 

 

 

  • Firoj's avatar
    Firoj
    New Member
    8 years ago

    let
    SQLSource = (param1 as text) =>
    let
    Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
    in
    Source
    in
    SQLSource

     

    This will Work 

     

     

     

    Regards,

    Firoj Shaikh

    [email protected]

  • azizimranz's avatar
    azizimranz
    Frequent Visitor
    8 years ago

    SvenTexas

     

    let
    SQLSource = (Start_Date as date, End_Date as date, Fund_Name as text) =>

     

     

    Not sure... is this what you were looking?

  • sesin's avatar
    sesin
    New Member
    8 years ago

    I use like  '"&Text.From(Period)& "'

  • sesin's avatar
    sesin
    New Member
    8 years ago

    I use like  '"&Text.From(Period)& "'

  • Firoj's avatar
    Firoj
    New Member
    8 years ago

    let
    SQLSource = (param1 as text) =>
    let
    Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
    in
    Source
    in
    SQLSource

     

     

     

    This should work 

    Regards,

    Firoj Shaikh.

    [email protected]