Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Stored Procedures in Direct Query mode

Hai,  I am working in powerbi for 1(1/2) months. My client requirement is to use parameters in the report. I want to make it happen using direct query. Via import it is possible... For direct que...
Need Help
v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi Anonymous ,

 

First connect to SQL using the Direct Query mode, then change the query to the following format in the Advanced editor.

SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>')

 

For example:

let
    Source = Sql.Database(".", "CaseTest", [Query="SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;trusted_connection=yes;','EXEC CaseTest.dbo.GetProductDesc_withparameters @PID=" & Parameter1 & "')"  ])
in
    Source

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi v-kkf-msft 

    Thank you for your answer. But the problem is still not resolved. 

    ERROR:

    My Expected Solution is:

    In import method whenever we use EXEC storeprocedure parameters, the query automatically get refresh and fetch data according the parameter values.

    For example:

    If I use select * from Animals

    It will result in some data with some columns like color, type, age. For this table, when am using slicer in desktop, resulting as empty table for some particular value.

    But instead of using select * if we use EXEC animals color,type,age it will refresh the table and give the expected answer.

    Like this am expecting via direct query.

     

    Hope this am clearly explained my requirement. 

    Regards,

    Karthika