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

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

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,

 

 

 

 

 

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

 

simpleQuery.PNG

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!