Forum Discussion

carlosDash's avatar
carlosDash
Advocate V
9 years ago
Solved

How do I pass parameters to my SQL statement?

How can I use parameters to create a dynamic SQL statement? I specified two parameters "DateStart" and "DateEnd" which I want to include in my Data source's SQL statement, but I don't know what the p...
Need Help
  • carlosDash's avatar
    carlosDash
    9 years ago

    I got it to work. Here is my code ( DateBegin & DateEnd parameters have been set to 'text'):

     

    let
    StartDate=DateBegin,
    EndDate=DateEnd,
    Source = Sql.Database("this-is-the-database-url.com", "TableNameHere", 
    [Query="SELECT * FROM [TableNameHere].[dbo].[Logs] WHERE CreatedTime >='" &StartDate& "' AND CreatedTime <='" &EndDate& "' ", 
    CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
    Source

    Oddly enough, I got a bunch of error messages when exiting the 'Query Editor' (as show in my previous post) but when I created everything again from scratch, it works perfectly. 

     

    I then saved the Power BI document as a template. When you open the template, you get prompted to enter the "DateBegin" and "DateEnd" parameters. Enter the parameters and the SQL query gets made, voila! :cathappy:

carlosDash's avatar
carlosDash
Advocate V
9 years ago

I got it to work. Here is my code ( DateBegin & DateEnd parameters have been set to 'text'):

 

let
    StartDate=DateBegin,
    EndDate=DateEnd,
    Source = Sql.Database("this-is-the-database-url.com", "TableNameHere", 
    [Query="SELECT * FROM [TableNameHere].[dbo].[Logs] WHERE CreatedTime >='" &StartDate& "' AND CreatedTime <='" &EndDate& "' ", 
    CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
    Source

Oddly enough, I got a bunch of error messages when exiting the 'Query Editor' (as show in my previous post) but when I created everything again from scratch, it works perfectly. 

 

I then saved the Power BI document as a template. When you open the template, you get prompted to enter the "DateBegin" and "DateEnd" parameters. Enter the parameters and the SQL query gets made, voila! :cathappy:

peterhinton's avatar
peterhinton
Frequent Visitor
9 years ago

hey ,

 

where you define

CreatedTime >='" &StartDate& "'

 

This assuming the StartDate is text,

 

How would i do it for an integer in my below statement

Where [Store No]= '" &Branch& "'"

 

My Store Nos are all integer

i currently get error message :

Expression.Error: We cannot apply operator & to types Text and Number.

  • smfaizhaider's avatar
    smfaizhaider
    Regular Visitor
    8 years ago

    peterhinton

    Please convert data type using Number.ToText(param)

  • brandonmc's avatar
    brandonmc
    Regular Visitor
    8 years ago

    Here's an example I got working using the AdventureWorks DB.


    I created a sproc that accepts one int parameter.  I created a parameter in PowerBI of type ANY called IDIN.  

     

    Here's my query:

     

    = Sql.Database(".\MSSQLSERVER2016", "AdventureWorksDW2014", [Query="Exec dbo.testfrompbi "&IDIN&"", HierarchicalNavigation=true])

     

     

  • carlosDash's avatar
    carlosDash
    Advocate V
    9 years ago

    It's been a long time since I last touched this.  I don't remember the syntax off the top of my head but did you try to mess around with the " " surrounding the parameter statement? Try removing them?

    • peterhinton's avatar
      peterhinton
      Frequent Visitor
      9 years ago

      the column of data i was working with in my table had the numbers saved as 'FLOAT'  format, i was able to convert them to propper integers and it now worked, appreciate your reply though, Thank You,