Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query

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
Using SQL Statement in Power Query for Excel
Using SQL Statement in Power Query for Excel
  1. Click the dropdown on the Load button
  2. Click Load to
Load to Options to Load the Results of Power Query query into an Excel Sheet or PowerPivot Model

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.

  1. Select Only Create Connection
  2. Check the Add this data to the Data Model option
  3. Click OK
Loading the Power Query Data into PowerPivot in Excel
Loading the Power Query Data into PowerPivot in Excel
  1. Rename the query to Bill of Materials
  2. On the Rename Query warning, click the Rename button
Renaming a Power Query query in Excel
Renaming a Power Query query for Excel
  1. The idea is to pass the parameters’ values from an Excel sheet, so I type the following into the Sheet1:
Entering Values in Excel that will be passed to SQL Server Stored Procedure
Entering Values in Excel that will be passed to SQL Server Stored Procedure
  1. Select the range we entered in the previous step
  2. Click the From Table/Range from the Data tab
  3. On the Create Table window, ensure that the My table has headers is ticked
  4. Click OK. This navigated us to the Power Query Editor window
Getting Data from a Table or Range in Power Query for Excel
Getting Data from a Table or Range in Power Query for Excel
  1. Rename the new query to Parameters
Renaming Queries in Power Query Editor in Excel
Renaming Queries in Power Query Editor for Excel

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}
Getting the First Value of a Column in Power Query
Getting the First Value of a Column in Power Query for Excel
  1. Select the Bill of Materials query from the Queries pane
  2. Click the Advanced Editor
  3. 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
  1. Click Done
Referencing Parameters Table from the SQL Statements
Referencing Parameters Table from the SQL Statements

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

Formula.Firewall: Query 'Bill of Materials' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Formula.Firewall: Query ‘Bill of Materials’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

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:

  1. Click the File menu
  2. Click Options and settings
  3. Click Query Options
  4. Click the Privacy tab under the CURRENT WORKBOOK section
  5. Click the Ignore the Privacy Levels and potentially improve performance
  6. Click OK
Ignoring the Privacy Levels in Power Query for Excel
Ignoring the Privacy Levels in Power Query for Excel

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

The Query Results After Parameterising the SQL Statement
The Query Results After Parameterising the SQL Statement

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:

Creating Named Ranges in Excel
Creating Named Ranges in Excel

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
Opening Power Query Editor in Excel and Creating a new Blank Query
Opening Power Query Editor in Excel and Creating a new Blank Query
  1. In the formula bar, type = Excel.CurrentWorkbook() and commit the change
Using the Excel.CurrentWorkbook() Function in Power Query for Excel
Using the Excel.CurrentWorkbook() Function in Power Query for Excel

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:

Viewing a cell's values in Power Query
Viewing a cell’s values in Power Query

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
  1. Click Done
Parameterizing SQL Statements of a SQL Connector in Power Query for Excel
Parameterising SQL Statements of a SQL Connector in Power Query for Excel

Here are the results:

The Results of Parameterizing SQL Statements of a SQL Connector in Power Query for Excel
The Results of Parameterising SQL Statements of a SQL Connector in Power Query for Excel

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
Enabling the Queries and Connections Pane in Excel
Enabling the Queries and Connections Pane in Excel
  1. Change the StartProductID‘s value from 727 to 802
  2. Click the Refresh button on the Bill of Materials (2)
Refreshing Queries from the Queries and Connections Pane
Refreshing Queries from the Queries and Connections Pane

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

Viewing a Sample of the Data of Power Query queries in Excel
Viewing a Sample of the Data of Power Query queries in Excel

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
Opening PowerPivot in Excel
Opening PowerPivot in Excel
  1. In Excel, change the value of the StartProductID back to 727
  2. Switch back to PowerPivot and click the Refresh button
Refreshing the Data in PowerPivot in Excel
Refreshing the Data in PowerPivot in Excel

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'
Reconciling the Results by Running the Query in SSMS
Reconciling the Results by Running the Query in SSMS

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.

Leave a Reply

Your email address will not be published.