Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

15 REPLIES 15
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,

Any news on an ETA when this will work??

Was the issue resolved?


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

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

 

 

 

 

 

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

 

simpleQuery.PNG

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.