Forum Discussion

jsBrizius's avatar
jsBrizius
Helper I
10 years ago
Solved

Why is DECLARE not supported (Error)?

    It seems for some reason a DECLARE in the SQL Statement does not work?   Here is a simple example...    
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    Hi jsBrizius,

     

    We reproduced same issue as yours. Power BI Desktop will always resolve the front end input SQL statement as a derived table. So when passing the SQL statement, the "Decalare"statement cause syntax error. See the fetched query in SQL profiler:

     

     

     

    We have reported this issue internally.

     

    Regards,

Vicky_Song's avatar
Vicky_Song
Impactful Individual
10 years ago

jsBrizius, I got the information from PG that the issue you get is a by design behavior. Please check the followings as detailed information:

 

This is currently working as designed.  DirectQuery and Import use the SQL statement differently.  Import will execute the SQL statement directly, but DirectQuery uses the SQL statement as a sub-query such as:

 

SELECT [Column1], SUM([Column2])

FROM (<SQL statement>) AS t

GROUP BY [Column1]

 

As a result, there is a requirement that the SQL statement must be a valid subquery which has restrictions not applied to standalone SQL statements.  Not allowing local variables is one of the restrictions.  To work around the restriction, the user can define a table valued function which does allow local variables and then import the table through the table valued function instead of the SQL statement directly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I'm having trouble working around the restrictions. Any chance you can elaborate, please?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I tried to create a function but I get the error "Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.  What I read was that you can't use dynamic SQL in a UDF. Any alternatives to get my dynamic SQL to pull into Power BI as a direct query?

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks Vicky_Song for the explanation. It does seem that the direct query has serious limitations. For example, you cannot execute a stored procedure (i.e. exec usp_something) as a direct query. As a result, you lose out on the auto update of the data model. So either

    • Rewrite your data collection stored procedure as a view (if possible/practical) OR
    • Use the Data Connectivity mode import instead of direct query - and schedule your updates through the gateway.

    If you need your stored procedures for your other apps, make the view first (for the direct query) then wrap the view in a stored proc (for your other apps).

     

    Not great but just about workable (and a year old - has there really been no change to accommodate this?).