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.

     

     

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.

 

 

Fowmy's avatar
Fowmy
Super User
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.