
Execute SQL Server Stored Procedure With User Parameter In Power BI
Overview
Sometimes, there is a situation when we need to get the data by consuming SQL Server Stored Procedure. SQL Server Stored Procedures has parameters which we need to pass dynamically.
Power BI provides a functionality to execute Stored Procedure using Managed Parameters.
In this article, we will talk about the following.
How to create Manage Parameter
How to pass manage parameter to a Stored Procedure
Invoke Query Result and load data to Power BI Desktop
Limitation
This feature will work only for Import Mode.
Example
I have one procedure in SQL Server named “sp_getEmpshiftDetails” which has two parameters named “vStartDate” and “vEndDate”. I want to use this procedure and load the data into Power BI Desktop. I have attached the file with this article for practice purposes.
So, now let’s get started!
Step 1 - Create Manage Parameter in Power BI Desktop
Open Power BI Desktop and from Home tab, select “Edit Queries”.
Click on “Manage Parameters” and select “New Parameter”.
It will open a popup to create a new parameter. Select “New”.
It will ask for the following information.
Name = Name of the parameter
Description = Description of Parameter
Required checkbox
Type = Datatype of a parameter
Suggested value
Current value