Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rob_s_m
New Member

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 1
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.