In September 2014, I wrote a blog post on dynamically passing parameters from PowerPivot to a SQL Server stored procedure using VBA. Back then, VBA was a real lifesaver. It perhaps still is for many of us. But frankly, I even forgot how to write VBA. Maybe it is time to look at it again. I also wrote a quick tip in August 2020 about doing a similar thing in Power BI using Query Parameters. Check it out if you’re keen to know how it works in Power BI.
Eight 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 thought it would be good to cover this topic after 8 years and see how it works now. So, here it is, a new blog post.
The Problem
From time to time, Excel users require to get the data from a SQL Server stored procedure. The stored procedures usually accept some input parameters and return the results. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server?
Prerequisites
For this blog post, I use SQL Server 2019 and Microsoft’s famous sample database, AdventureWorks2019. You can find Microsoft’s other sample databases here. I also use Excel 365, it should work the same way in Excel 2019, though.
The Solution
I discuss two approaches to overcome the challenge. Both approaches use Power Query slightly differently. In both approaches, we parameterise the SQL Statement of the SQL Server connector, passing the values to the parameters from an Excel table. One approach requires ignoring the Privacy Levels in Power Query, while the other does not. Both approaches work, but, depending on your preferences, you may prefer one over the other.
As mentioned, I use the AdventureWorks2019 sample database that contains a couple of stored procedures. I use the dbo.uspGetBillOfMaterials stored procedure accepting two parameters, @StartProductID and @CheckDate.
Approach 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privacy Levels
Follow these steps to pass the parameters’ values from an Excel sheet to the stored procedure and get the results in Excel:
- In Excel, navigate to the Data tab
- Click the Get Data dropdown
- Hover over the From Database option and click the From SQL Server Database
- Enter the Server
- Enter the Database
- Expand the Advanced options
- Type
EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 727, @CheckDate = N'2013-01-01'in the SQL statement textbox - Click OK
- Click the dropdown on the Load button
- Click Load to
From here, we have some options to load the results either into an Excel sheet or the PowerPivot data model. We want to load the data into the PowerPivot data model in this example.
- Select Only Create Connection
- Check the Add this data to the Data Model option
- Click OK
