Forum Discussion

vsethireuters's avatar
vsethireuters
New Member
9 years ago
Solved

Calling a stored procedure before calling a view in sql server

Hi there 

 

I am new to PowerBI and am hoping you could help with my proof of concept here. 

 

I need to load data for a report by calling a view but beforee that need to authentciate and create a session by calling a stored procedure. 

 

My database is SQL Server

 

Whats the best way to achieve this ? 

 

Thanks for your help in advance .

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi vsethireuters,

     

    Based on test, Eric’s solution works on my side, you can refer to below steps:

     

    1. Use import data mode to connect SQL server.

     

    2. Open the query editor, open the advanced editor, copy the code of source.

     


    3. Add a new blank query, paste source code and modified to below query.

     

    let 
    SQLSource = (SPName as text, param as text) => 
let
    Source =  Sql.Database("xxxxxx", "xxxxx", [Query="exec "&SPName&" '"&param&"'"])
in
    Source
in
    SQLSource

     

     

     

    4. Fill in the parameters and click on invoke button.

     


     

    Notice: Eric’s solution not work on direct query mode.

     

    Regards,
    Xiaoxin Sheng

3 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    9 years ago

    For calling a stored procedure using PowerBI,

     

    Please refer to this  community link by Eric Zhang for more information.

    • vsethireuters's avatar
      vsethireuters
      New Member
      9 years ago

      Thanks for your replay 

       

      I tried this 

      let
      Source = Sql.Database("SERVER IP", "DB NAME", [Query="EXEC session_authenticate 'act_name'"])
      let
      Source = Sql.Database("SERVER IP", "DB NAME"),
      nameofview_vw = Source{[Schema="dbo",Item="nameofview_vw"]}[Data]
      in
      Source
      in
      nameofview_vw

       

       but get an error = Token Comma Expected

       

      Any ideas ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi vsethireuters,

         

        Based on test, Eric’s solution works on my side, you can refer to below steps:

         

        1. Use import data mode to connect SQL server.

         

        2. Open the query editor, open the advanced editor, copy the code of source.

         


        3. Add a new blank query, paste source code and modified to below query.

         

        let 
    SQLSource = (SPName as text, param as text) => 
let
    Source =  Sql.Database("xxxxxx", "xxxxx", [Query="exec "&SPName&" '"&param&"'"])
in
    Source
in
    SQLSource

         

         

         

        4. Fill in the parameters and click on invoke button.

         


         

        Notice: Eric’s solution not work on direct query mode.

         

        Regards,
        Xiaoxin Sheng