Issue while using stored procedure in SQL server database

[powerbidev123]

Hi community,

Is there a way to fix this because manually inserting the customerid works but when I try to make it parameterized then it gives the above error

Any kind of suggestion would be helpful

Thanks

[v-huijiey-msft]

Hi @powerbidev123 ,

My following up is just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft]

Hi @powerbidev123 ,

My following up is just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft · Accepted solution]

Hi @powerbidev123 ,

Thanks for the reply from girishthimmaiah .

Assuming that your

Server = X

Database = Y

Table name = Z

The sample SQL query is (as if you put it on SSMS)

select *
from z
WHERE [customerid]=2

The conversion to M-code is:

let
    Source = Sql.Database("vm1", "TutorialDB", [Query="select *#(lf)from Customers#(lf)WHERE [customerid]=2#(lf)"])
in
    Source

Now, if you want to pass parameters to the where clause as Parameter1, (e.g. Parameter1 =2) make sure they are text first.

You can create a custom query as follows:

let
    Source = Sql.Database("x", "y", [Query="select *#(lf)from z#(lf)WHERE [customerid]="&Parameter1&"#(lf)"])
in
    Source

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[girishthimmaiah]

80% of your text is likely AI-generated

New version:
It seems that Power BI is attempting to execute a stored procedure (SI_PowerBI_TalentPool), but it requires a @customerid parameter that isn't being passed correctly. When you input the customer ID manually, it works fine, but when you try to make it dynamic (likely using a parameter or slicer), it doesn't function as expected.

How to Fix It
Here are a few suggestions you can try:

1. Properly Define the Parameter in Power Query
Open the Power Query Editor in Power BI.
Navigate to Manage Parameters → New Parameter.
Name it customerid, and set its type to Text or Number (depending on what your SQL procedure requires).
Assign a default value (just for testing purposes).
2. Adjust the SQL Query to Incorporate the Parameter
If you're writing the query directly in Power BI, you might have something like:

sql

EXEC SI_PowerBI_TalentPool @customerid = '12345'
Instead, modify it to:

sql

EXEC SI_PowerBI_TalentPool @customerid = @customerid.