Help Using SQL Stored Procedure Parameteric and no...

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 ,

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!