Forum Discussion
Why is DECLARE not supported (Error)?
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,
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.
I'm having trouble working around the restrictions. Any chance you can elaborate, please?