Forum Discussion

DukeCarey's avatar
DukeCarey
Regular Visitor
2 years ago
Solved

Pass date parameter to a SQL function

I need to pull data via a SQL function that requires a date parameter.  The SQL query is very simple: SELECT * FROM dbo.FunctionName('20230630').     All the search results I've found focus on usin...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    DukeCarey 

    I created a parameter, called startdate, in your case it is linked to an Excel sheet, it;s fine. Here is the modified code, which should work for as well. Modify the Date format as per your source system.

    let
    DateParam = "'" & Date.ToText(StartDate, "yyyy-MM-dd") & "'",
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase(" & DateParam & ")", CreateNavigationProperties=false])
in
    Source

      

  • Fowmy's avatar
    Fowmy
    2 years ago

    DukeCarey 

    As there are two data sources talking to each other, the privacy level needs to be set, you may set it to None in this case: Go to Options and Settings then set it up for each source.

     

     

Fowmy's avatar
Fowmy
Super User
2 years ago

DukeCarey 
When you connect using the SQL as the source, your query should look like this. I created a function with a date parameter and and it works:

let
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase('2004-03-14')", CreateNavigationProperties=false])
in
    Source

 

DukeCarey's avatar
DukeCarey
Regular Visitor
2 years ago

Thanks for the response.  I should have been a little clearer with my question.  I'm setting this up for a user who will be unwilling/unable to edit a single query to use a different date, and in this case there are several queries in the workbook that will use the date. Only one will use it as the input to the SQL function, though.

 

So, the goal is to use an Excel table (residing in K1:K2) where the user could change the date and the one query would use THAT date for the function's parameter, while the other queries will filter their results using the same date parameter.

I hope I've made that clearer rather than confusing the issue

The particulars are that I've converted the table into a parameter named StartDate that can be used to filter other queries, but I also want it to go in as the parameter to that SQL function.

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    DukeCarey 

    I created a parameter, called startdate, in your case it is linked to an Excel sheet, it;s fine. Here is the modified code, which should work for as well. Modify the Date format as per your source system.

    let
    DateParam = "'" & Date.ToText(StartDate, "yyyy-MM-dd") & "'",
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase(" & DateParam & ")", CreateNavigationProperties=false])
in
    Source

      

    • DukeCarey's avatar
      DukeCarey
      Regular Visitor
      2 years ago

      Thanks Fowmy, I think we're inching closer now.  After mimicking what you posted I ran into the formula.firewall error and I'm stumped there now.

       

      Formula.Firewall: Query 'PivotPurchaseTypesSince' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

       

       

      • DukeCarey's avatar
        DukeCarey
        Regular Visitor
        2 years ago

        BTW, I tried to "flatten" the query based on a blog post and tried this:

        let
        SourceX = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
        Column1 = #"Changed Type"{0}[Column1],
        DateParam = "'" & Date.ToText(Column1, "yyyy-MM-dd") & "'",

        Source = Sql.Database("test.database.windows.net", "testdata", [Query="SELECT * FROM rpt.PivotPurchaseTypesSince(" & DateParam & ")"])
        in
        Source

         

        but then got this error:

        Expression.Error: A cyclic reference was encountered during evaluation.