Forum Discussion
pass cell value to sql stored procedure
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.
1 Reply
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
