SQL query in direct query mode

---

amitabhk1971 wrote:

Well I am using only the select part of the stored procedue. I am not using exec procname. 

---

amitabhk1971

In my test on SQL Server, in DQ mode, Power BI sends a query wrapped as below.

SELECT XXXX, XXXX, XXX, XX FROM (

your query in you input in the pbi desktop

) t

So in your case, the actually query is 

SELECT XXXX, XXXX, XXX, XX FROM (

declare ....
...
select..

) t

That's why the you got the SQL syntax error. This is on SQL Server, and I think Power BI does the same thing to Azure SQL. As a workaround, instead of DECLARE statement, replace those variables in the SQL with a scalar value sub query.The CTEs can't work in this case as well, Try to recompose the SQL like.

From

FROM

DECLARE @LatestPackageVersion int;
SELECT
@LatestPackageVersion = MAX(PackageId)
FROM [Packages];
DECLARE @CurrentTime AS datetime;
SELECT
@CurrentTime = GETUTCDATE()

;with cte as(
....
where datetime = @CurrentTime
and version = @LatestPackageVersion
)
select * from cte

TO

SELECT * FROM (
..
where datetime = GETUTCDATE() 
and version = (select MAX(PackageId) FROM [Packages])

) cte

Thanks Eric. That is what I did. I replaced the parameters with their select queries and now it works.