Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query - BI Insight (2024)

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:

1. In Excel, navigate to the Data tab
2. Click the Get Data dropdown
3. Hover over the From Database option and click the From SQL Server Database
4. Enter the Server
5. Enter the Database
6. Expand the Advanced options
7. Type EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 727, @CheckDate = N'2013-01-01' in the SQL statement textbox
8. Click OK

9. Click the dropdown on the Load button
10. 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.

11. Select Only Create Connection
12. Check the Add this data to the Data Model option
13. Click OK

14. Rename the query to Bill of Materials
15. On the Rename Query warning, click the Rename button

16. The idea is to pass the parameters’ values from an Excel sheet, so I type the following into the Sheet1:

17. Select the range we entered in the previous step
18. Click the From Table/Range from the Data tab
19. On the Create Table window, ensure that the My table has headers is ticked
20. Click OK. This navigated us to the Power Query Editor window

21. Rename the new query to Parameters

We now want to change the SQL Statement of the Bill of Materials query to get the values from the relevant columns in the Parameters table. Therefore, whenever the user changes the values on Excel, the results of the SQL Statement in the Bill of Materials table change accordingly. In Power Query, we can reference the first value of a column in a table as below:

TABLE_NAME[COLUMN_NAME]{0}

So, we use the following expression to get the value of the StartProductID column from the Parameters table:

Parameters[StartProductID]{0}

22. Select the Bill of Materials query from the Queries pane
23. Click the Advanced Editor
24. Replace the original expression with the following

let Source = Sql.Database(".\sql2019", "adventureworks2019", [Query="EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = " & Text.From(Parameters[StartProductID]{0}) & ", @CheckDate = '" & Text.From(Parameters[CheckDate]{0}) & "'"])in Source

25. Click Done

Depending on your Power Query privacy settings in Excel, you may get the following warning:

We get this warning because we are referencing the Parameters query from the Bill of Materials query, while these queries are coming from different data sources, which means we are potentially sending data from one data source to another. This can be a potential privacy breach, hence Power Query’s internal firewall blocks the referencing query. In our scenario, we can ignore the Privacy Levels. I suggest reading this document to understand the Privacy Level settings in Power Query and this one about Privacy Level Firewall before deciding to ignore the Privacy Levels.

To ignore the Privacy Levels, follow these steps:

26. Click the File menu
27. Click Options and settings
28. Click Query Options
29. Click the Privacy tab under the CURRENT WORKBOOK section
30. Click the Ignore the Privacy Levels and potentially improve performance
31. Click OK

Now, the Bill of Materials query should show the results as the following image shows:

Let us have a look at the second approach.

Approach 2: Parameterising the SQL connector’s SQL Statements, Without Ignoring Privacy Levels

In this approach, we do not load the parameters table from Excel into Power Query. Instead, we use the Excel.CurrentWorkbook() function, which gives us all Excel tables and named ranges in the current workbook. Let us have a quick look and see how the Excel.CurrentWorkbook() works.

Using the Excel.CurrentWorkbook() in Power Query for Excel

As the following image shows, I added some dummy data, value1 and value2, in two cells in Excel. I can create a Named Range by selecting multiple cells and typing a name in the Name Box:

Now we open the Power Query Editor and go through the following steps:

1. Click the Get Data drop down
2. Click the Launch Power Query Editor
3. In the Power Query Editor window, create a Blank Query by right-clicking the Queries pane and hovering over the New Query > Other Sources > Blank Query

4. In the formula bar, type = Excel.CurrentWorkbook() and commit the change

In the above image, the Params table is the table we created earlier when we were developing the first approach, which contains the values we want to pass to the SQL Statement of the SQL connector. To see the contents of each table, click on the cell (not on the Table) as shown in the following image:

We are after the value of the StartProductID column to pass to the @StartProducyID parameter and the values of the CheckDate column to pass to the @CheckDate parameter of the dbo.uspGetBillOfMaterials stored procedure in our SQL Statement. With the following expressions, we can the values:

• StartProductID: Excel.CurrentWorkbook()[Content]{0}[StartProductID]{0}
• CheckDate: Excel.CurrentWorkbook()[Content]{0}[CheckDate]{0}

Now that we know how the Excel.CurrentWorkbook() function works, and how to get the values for the StartProductID and CheckDate, we go ahead modifying the SQL Statement. To have both approaches in the same file, I duplicate the Bill of Materials query. I also change the Privacy Level setting back to default. Follow these steps to implement the second approach:

1. Select the duplicated query, Bill of Materials (2)
2. Click the Advanced Editor button
3. Replace the previous expressions with the below one:

let Source = Sql.Database(".\sql2019", "adventureworks2019", [Query="EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = " & Text.From(Excel.CurrentWorkbook()[Content]{0}[StartProductID]{0}) & ", @CheckDate = '" & Text.From(Excel.CurrentWorkbook()[Content]{0}[CheckDate]{0}) & "'"])in Source

4. Click Done

Here are the results:

It is all done. We can now click the Close & Load button from the ribbon.

I like the second approach over the first one, so I unload the Bill of Materials query from the PowerPivot model and keep the Bill of Materials (2) query. Now that we successfully implemented the solution, let us test it to ensure it works as expected.

Testing the Solution

Testing the solution is easy. We only need to change the values of the parameters on the Excel sheet and refresh the PowerPivot model. We have two options to do so.

Option 1, Using the Queries and Connections pane in Excel

1. Click the Queries and Connections from the Data tab on the ribbon. Currently, the Bill of Materials (2) query has 24 rows

2. Change the StartProductID‘s value from 727 to 802
3. Click the Refresh button on the Bill of Materials (2)

As the preceding image shows, the query now contains 8 rows. To view the data, hover over the query:

The data in the above image is loaded into the PowerPivot model, so we can always look at PowerPivot to see the actual data, which takes us to the second approach.

Option 2, Using the PowerPivot in Excel

In this approach, we change the values in the Excel sheet and refresh the data directly from the PowerPivot.

1. Click the Manage Data Model button from the Data tab on the ribbon to open PowerPivot

2. In Excel, change the value of the StartProductID back to 727
3. Switch back to PowerPivot and click the Refresh button

Finally, you might ask about how you can reconcile the data in our tests. Well, we only need to open SQL Server Management Studio (SSMS) and run the following SQL statement:

EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 802, @CheckDate = N'2013-01-01'

That is it. We made it. I hope you find this blog post helpful. If you have questions or comments, use the comments section below to communicate with me.

Related