SQL parameters in direct query

[amitabhk1971]

Hi,

I am using the below query as part of a sql query to generate report in Direct Query mode. It throws an error while loading the dataset. It says there is a syntax error at DECLARE. But when I open the query editor it does not show any syntax error. I think in Direct Query mode it does not accept DECLARE @variablename ....... statement. Can someone suggest a workaround? 

DECLARE @dates TABLE(dt datetime);
DECLARE @dateFrom datetime;
DECLARE @dateTo datetime;
SET @dateFrom = dateadd(dd,-7,getutcdate())
SET @dateTo = getutcdate()
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(minute,15,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END;

This piece of query is at the top of the full query and then I use @dates  parameter at different places in the sql query. This works fine in import mode. But I need Direct Query mode to run this.

Thanks,

Amitabh

[Eric_Zhang · Accepted solution]

---

@amitabhk1971 wrote:

Hi,

@I am using the below query as part of a sql query to generate report in Direct Query mode. It throws an error while loading the dataset. It says there is a syntax error at DECLARE. But when I open the query editor it does not show any syntax error. I think in Direct Query mode it does not accept DECLARE @variablename ....... statement. Can someone suggest a workaround? 

@DECLARE @dates TABLE(dt datetime);
@DECLARE @dateFrom datetime;
@DECLARE @dateTo datetime;
@set @dateFrom = dateadd(dd,-7,getutcdate())
@set @dateTo = getutcdate()
@WHILE(@dateFrom < @dateTo)
BEGIN
@SELECT @dateFrom = DATEADD(minute,15,@dateFrom)
@INSERT INTO @dates
@SELECT @dateFrom
END;

@This piece of query is at the top of the full query and then I use @dates  parameter at different places in the sql query. This works fine in import mode. But I need Direct Query mode to run this.

Thanks,

Amitabh

---

@amitabhk1971

In DirectQuery mode, Power BI sends the SQL Statement as below, that's why you got the invalid syntax.

SELECT XXX, XXX, XXX FROM
(
the sql statement input, in your case, the statement starts with DECLARE
)

As a workaround, you can create a built-in calendar table with 15 minutes difference of each datetime row and query from Power BI desktop instead of generating the table on  the fly.

let
    Source = Sql.Database("yourserver", "yourdb", [Query="select * from calendar where dt between '"&Date.ToText(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-7), "yyyy/MM/dd")&"' and '"&Date.ToText(Date.From(DateTimeZone.UtcNow()), "yyyy/MM/dd")&"'"])
in
    Source