Forum Discussion

powerbidev123's avatar
powerbidev123
Icon for Solution Sage rankSolution Sage
1 year ago
Solved

Issue while using stored procedure in SQL server database

 

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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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!

4 Replies

  • girishthimmaiah's avatar
    girishthimmaiah
    Icon for Resolver I rankResolver I
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!