Search

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.

  1. Name = Name of the parameter

  2. Description = Description of Parameter

  3. Required checkbox

  4. Type = Datatype of a parameter

  5. Suggested value

  6. Current value