Why is DECLARE not supported (Error)?

[jsBrizius]

It seems for some reason a DECLARE in the SQL Statement does not work?

Here is a simple example...

[Anonymous]

Hi,

I made a new function (new source ---> Blank query) and had same issue with this query:

let
UserNameSQL = Sql.Database("Servername","DatabaseName",[Query="EXEC [proj].[TestOrganisation2]"])

in
UserNameSQL

but i solved it by changing it to this:

let
UserNameSQL = () => 
Sql.Database("Servername","DatabaseName",[Query="EXEC [proj].[TestOrganisation2]"])

in
UserNameSQL

Hope it can help you guys 🙂

regards

[vissvess]

But what is this in case of Direct query..!
This converting the query into function and then invoking to a query and loading is converting the query to import mode..

What should I do for Direct query.

[HarshVidap]

I have the same issue, were you able to find the solution?

[Vicky_Song]

@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.

[numbus]

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?). 

[Anonymous]

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]

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

[jsBrizius]

I am getting an error when I try to Close & Apply the Query Editor.  My query seems to work fine until I try to Close and Apply.

[ankitpatira]

@jsBrizius I can reproduce your issue. But looking at your query you can get away without using Declare. You can instead use each of those functions in line.

[jsBrizius]

Is this a bug?

My query is much harder to read without the ability to declare a variable.  Seems strange that it works fine in the Query Editor.

[v-sihou-msft · Accepted solution]

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,

[HarshVidap]

Was the issue resolved?

Update: The issue wasn't resolved and you still can't use temp tables or declare statements.

[WTsrnl]

Maybe we can make this into a new Topic, since this is probably not at the top of their list, or even close to the first 5 pages of features.

That way, we can trigger a new response

[jsBrizius]

So apparently you cannot use DECLARE?  I still get the same error with the following query.