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.