Hover over the From Database option and click the From SQL Server Database.

I use the dbo.uspGetBillOfMaterials stored procedure accepting two parameters, and Approach 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privacy Levelsįollow these steps to pass the parameters’ values from an Excel sheet to the stored procedure and get the results in Excel: Both approaches work, but, depending on your preferences, you may prefer one over the other.Īs mentioned, I use the AdventureWorks2019 sample database that contains a couple of stored procedures.

One approach requires ignoring the Privacy Levels in Power Query, while the other does not. In both approaches, we parameterise the SQL Statement of the SQL Server connector, passing the values to the parameters from an Excel table. Both approaches use Power Query slightly differently.

I discuss two approaches to overcome the challenge. I also use Excel 365, it should work the same way in Excel 2019, though. You can find Microsoft’s other sample databases here. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server? Prerequisitesįor this blog post, I use SQL Server 2019 and Microsoft’s famous sample database, AdventureWorks2019. The stored procedures usually accept some input parameters and return the results. The Problemįrom time to time, Excel users require to get the data from a SQL Server stored procedure. I thought it would be good to cover this topic after 8 years and see how it works now. Check it out if you’re keen to know how it works in Power BI.Įight years later, one of my weblog readers asked how to do the same thing in later versions of Excel he is specifically asking for Excel 2019. I also wrote a quick tip in August 2020 about doing a similar thing in Power BI using Query Parameters.

But frankly, I even forgot how to write VBA. In September 2014, I wrote a blog post on dynamically passing parameters from PowerPivot to a SQL Server stored procedure using VBA.

Power query excel 2021