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 

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.

  • 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.