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

Greg_DecklerAnonymous I was actually able to do what I set out to do with the parameters, I just didn't know the correct syntax. I used the method described in “Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI” but just simply defined the variables as "DateBegin=DateBegin, DateEnd=DateEnd". It works great within the query editor but once I hit 'Close and apply', I get this error message:

 

I tried to find some solutions to overcome these but I wasn't able to find anything useful.  I don't really understand what the first error message means.

 

Trying to declare the @DateBegin variable with the SQL DECLARE and SET methods only resulted in an error message saying "@DateBegin has already been declared". 

 

Any ideas on how to get forward with these issues would be appreciated :smileyhappy: