cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsBrizius
Helper I
Helper I

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

 

Select.PNG

Result.PNG

 

Error.PNG

1 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:

 

Ca44pture.PNG

 

Captur5656e.PNG

 

We have reported this issue internally.

 

Regards,

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

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

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.

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

 

Vicky_Song
Impactful Individual
Impactful Individual

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

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
Not applicable

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?

 

 

Capture.PNG

 

Anonymous
Not applicable

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

jsBrizius
Helper I
Helper I

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.

 

Data.PNG

Query.PNG

ApplyQueryChanges.PNG

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

 

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.

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:

 

Ca44pture.PNG

 

Captur5656e.PNG

 

We have reported this issue internally.

 

Regards,

Was the issue resolved?

 

 

 

 

 

 

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

 

simpleQuery.PNG

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors