Tag: Stored Procedure

DAX, Excel, Power Pivot, Power Query

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
Continue reading “Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query” →
Business Intelligence - BI, Power BI, Power BI Desktop, Power BI Desktop RS, Power Query, Quick Tips, Self-Service BI, SQL Server, T-SQL

Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures

I have written 3 blogposts about query parameters in the past.

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:

CREATE PROCEDURE SP_Sales_by_Date 
	@date int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT *
	FROM [dbo].[FactInternetSales]
	WHERE OrderDateKey >= @date
END
GO

In Power BI Desktop, get data from SQL Server, then:

  • Enter Server name
  • Enter Database name
  • Select Data Connectivity Mode
  • Expand the Advanced options
  • Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
  • Click OK
Get Data From SQL Server using SQL Statements in Power BI Desktop
  • Click Transform Data
Transform Data in Power BI Desktop

Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:

Creating New Query Parameter in Power BI Desktop
Continue reading “Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures” →
Power BI, Power Query, SQL Server

Power Query and SQL Server Stored Procedures

Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials  to get the results.

If we execute the stored proc in SSMS using

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

, we’ll get the following result:

image

Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.

image

Now type Server, Database and SQL Statement, then click OK.

Continue reading “Power Query and SQL Server Stored Procedures” →