cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsBrizius Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Custom Query 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:

 

Ca44pture.PNG

 

Captur5656e.PNG

 

We have reported this issue internally.

 

Regards,

View solution in original post

10 REPLIES 10
jsBrizius Regular Visitor
Regular Visitor

Custom Query Error

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 Regular Visitor
Regular Visitor

Re: Custom Query Error

 

 

 

 

 

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

 

simpleQuery.PNG

ankitpatira Super Contributor
Super Contributor

Re: Custom Query Error

@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 Regular Visitor
Regular Visitor

Re: Custom Query Error

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.

Moderator v-sihou-msft
Moderator

Re: Custom Query 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:

 

Ca44pture.PNG

 

Captur5656e.PNG

 

We have reported this issue internally.

 

Regards,

View solution in original post

Vicky_Song Established Member
Established Member

Re: Why is DECLARE not supported (Error)?

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

verstreater86 Frequent Visitor
Frequent Visitor

Re: Why is DECLARE not supported (Error)?

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

verstreater86 Frequent Visitor
Frequent Visitor

Re: Why is DECLARE not supported (Error)?

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

 

numbus Frequent Visitor
Frequent Visitor

Re: Why is DECLARE not supported (Error)?

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 278 members 2,981 guests
Please welcome our newest community members: