pass cell value to sql stored procedure

[rob_s_m]

Working on a Power Query that calls a stored procedure from SQL Server. I can hard code a year, 2021 in this example, but I would like to parameterize the year.

let

    Source = Sql.Database("1.1.1.1", "SQLDatabase", [Query="exec [dbo].[OUT_StoredProc] 2021"])

in

    Source

The goal is to have the user change the year in an Excel workbook cell. I have gotten a query to pull the data from the cell and update when the year is changed:

let

    Source = Excel.CurrentWorkbook(){[Name="Year"]}[Content],

    YearVar = Source{0}[Column1]   

in

    YearVar

My question is the syntax for passing the “YearVar” to the stored procedure?  I.e., replacing the hard coded “2021” with “YearVar”?  All the things I have tried return an error.

[mahoneypat]

You will need to use a pattern like this, breaking the text string of your sql into parts.  FYI you could just use a parameter in your PBI report instead of separately storing the year value in an Excel workbook.

sqlquery = "first part of sql ... year = " & yearquery & " ... rest of sql"

Pat