Help Using SQL Stored Procedure Parameteric and not parametric in DirectQuery Mode in Power BI

[techVikas]

Hi Community,

I'm building a Power BI report using SQL Server as the data source. I want to use an existing stored procedure that takes parameters (e.g., @SiteCode), and I want to keep the connection in DirectQuery mode so the data reflects real-time changes.

Challenges I'm Facing:

The stored procedure is not visible in the navigator when connecting via DirectQuery.

Even when the stored procedure does not use temp tables, I get syntax errors like:

"Incorrect syntax near 'EXEC'"

Or schema inference errors during loading.

I tried using Value.NativeQuery() like this:
let
Source = Sql.Database("192.168.x.x", "MyDB"),
Result = Value.NativeQuery(Source, "EXEC xspMyProcedure @SiteCode='CWPFAB'")
in
Result
But this throws an error during data load or preview.

My Questions:

Is there a supported or recommended approach to using stored procedures with parameters in DirectQuery mode?

Why does Power BI fail to read schema even when temp tables are not used?

Is converting the logic to a view or table-valued function the only stable option?

Is there a way to dynamically pass parameters to the stored procedure from Power BI?

Any help or suggestions would be greatly appreciated!

Thanks,
Vikas

[v-sdhruv]

Hi @techVikas ,

Good to know that your issue got resolved.
Can you please mark the post as "Accept as solution" so that other users can also benefit from it?

Thank you for using Microsoft Community forum

[v-sdhruv]

Hi @techVikas ,

Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you

[techVikas]

Hi @v-sdhruv
your solution helped

thanks

[v-sdhruv]

Hi @techVikas ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If you need any assistance, let us know.

Thank You

[v-sdhruv]

Hi @techVikas ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If you need any assistance, let us know.

Thank You

[v-sdhruv]

Hi  @techVikas ,

If you dont need live data, I suggest you to use import  mode instead of direct query because it's not possible to use a query with common table expressions, nor one that invokes stored procedures.
Refer- Data Transformation Limitations

The resources suggested by Sam is a helpful workaround for this problem.

Additionally, you might to check out-

Can I execute a SP in DirectQuery Mode with or without parameters?

Hope this helps!

[SamWiseOwl · Accepted solution]

HI @techVikas 

Have you tried the method suggested here?
https://community.fabric.microsoft.com/t5/Desktop/Calling-SQL-stored-procedure-with-Direct-Query/td-...

A fully explanation here:
https://prologika.com/power-bi-dynamic-m-query-parameters-reloaded/

Alternatively Chris Webb did a blog about used a Table Valued Function instead:
https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-powe...