Help Needed: Passing Dynamic Parameters to SQL Stored Procedure in Power BI

[manoj_0911]

Help Needed: Passing Dynamic Parameters to SQL Stored Procedure in Power BI

Hi Community,

I am working on a Power BI project and need help with calling a **SQL stored procedure** dynamically in Power Query. My knowledge of stored procedures is very limited, so I'm struggling with setting this up.

Here's what I am trying to achieve:

1. I want to use a stored procedure (`p_Get_UStatus`) from my SQL Server database to fetch data.

2. The stored procedure requires the following parameters:

   - `@v_detail` – This will always be `'Agent_Details'`.

   - `@v_group_names` – A comma-separated list of group names (e.g., `'group1,group2'`).

   - `@v_start_time` – Start date.

   - `@v_end_time` – End date.

3. I need these parameters (`group names`, `start date`, `end date`) to be **dynamic** so that they can be controlled from Power BI.

**What I Have Tried So Far:**

1. I wrote the following query in Power Query's Advanced Editor:

   ```M

   let

       Source = Sql.Database("", "DEV", 

           [

               Query = Text.Format("EXEC dbo.p_Get_UStatus 

                                    @v_detail = 'Agent_Details', 

                                    @v_group_names = '#{0}', 

                                    @v_start_time = '#{1}', 

                                    @v_end_time = '#{2}'", 

                                   {SelectedGroups, StartDate, EndDate})

           ])

   in

       Source

   ```

2. I created parameters in Power BI for:

   - `SelectedGroups` (comma-separated list of groups).

   - `StartDate` (Date type).

   - `EndDate` (Date type).

3. When I run the query, I get errors like:

   - **Expression.SyntaxError: Token Literal expected.**

---

**Questions:**

1. Is this the right way to call a stored procedure with dynamic parameters in Power BI?

2. Do I need to change anything in the stored procedure itself?

3. How can I pass the `@v_group_names`, `@v_start_time`, and `@v_end_time` parameters dynamically?

4. If someone has experience with a similar setup, could you guide me step-by-step?

Any help or insights would be greatly appreciated! Thanks in advance.

----

[Anonymous]

I have the same issue.  I created a parameter with values tied to another table called SortBy which holds the valid list of parameters.

I have a slicer on the canvas tied to Sort Value

No action is taken when I select a value from that slicer.  

I've been reading about binding a field to a parameter, but I do not have that option in my modeling - advanced settings.  

Field parameters as a Preview Feature is checked in Options.

How can I expose the parameter the user so they can dynamically select a value that will refresh the visual with the correct passed parameter?

[Mahee]

HI @manoj_0911 ,

Could you pls confirm whether this approach works for azure sql mi as well? 

[Anonymous]

Hi @manoj_0911 ,

Please refer to the following M code.

let
    Source = Sql.Database("<Server_Name>", "<Database_Name>", 
    [Query="EXEC <Your_ProcedureName> 'Agent_Details',
    '"&SelectedGroups&"',
    '" &Date.ToText(StartDate) &"',
    '" &Date.ToText(EndDate) & "'"
    ]
    )
in
    Source

You can also refer to the following link for more information.

Execute SQL Server Stored Procedure With User Parameter In Power BI

 Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manoj_0911]

Thank you so much for your reply , How and Where to create the parameters (SelectedGroups, StartDate and EndDate parameters) correctly, please help, also if you could please share the pbix file

[Anonymous · Accepted solution]

Hi @manoj_0911 ,

Please refers to the following steps.

1.Open the Power Query editor and click Manage Parameters -> New to create a new parameter.

2. Create parameters in the Manage Parameters dialog box.

3.Then connect to the SQL Server database.

In this SQL statement, the procedure name is followed by a series of values that correspond to the procedure parameters.

EXEC <YourProcedureName> 'Agent_Details','Group1,Group2','2024-08-01','2024-09-01'

4.After importing the data, go to the Power Query Editor again and open the Advanced Editor for the query you just imported, and copy the M code I provided earlier.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manoj_0911]

I tried connecting powerbi to sql server stored procedure , i'm able to pass parameter values but, I'm not able to use them as Slicers/Filters in the Report.

[manoj_0911]

Thank you so much for you reply , let me try it first