Native Runtime Parameter Preservation for Value.NativeQuery() in Power BI

[dospencer]

Current Behavior

Value.NativeQuery() supports parameter placeholders during Power Query preview evaluation, but parameter bindings are not preserved during DirectQuery runtime execution after Close & Apply.

This causes runtime SQL execution failures such as:

Must declare the scalar variable @Param -- for MS SQL Server database
OR
 ORA-01008: not all variables bound -- for Oracle database

when the DirectQuery engine reconstructs SQL without rebinding parameters.

Why Existing Alternatives Are Insufficient

1. String Concatenation

Unsafe for text parameters.

Example:

Value.NativeQuery(
    Source,
    "SELECT * FROM Table WHERE Name = '" & Param & "'"
)

Problems:

• SQL injection exposure
• manual escaping complexity
• loss of type-safe parameterization
• inconsistent query plans

2. Stored Procedures

Not viable in many enterprise/vendor-managed environments:

• read-only access
• no schema modification permissions
• SaaS/vendor-controlled databases

3. Query Folding

Folding applies transformations after source query generation in many cases.

This does not support scenarios where parameters must:

• alter joins
• constrain CTEs
• influence optimizer behavior
• reduce scanned partitions
• control source-side execution logic

Suggested Feature

Add official runtime parameter binding support for Value.NativeQuery() in DirectQuery mode.

Value.NativeQuery(
    Source,
    "
    SELECT *
    FROM Sales
    WHERE SaleDate >= @StartDate
      AND SaleDate < @EndDate
    ",
    [
        StartDate = StartDateParam,
        EndDate   = EndDateParam
    ]
)

with guaranteed preservation of:

• parameter metadata
• parameter values
• parameter types
• prepared statement semantics

through the full DirectQuery execution pipeline.

Additional Enhancement Ideas

Microsoft could strengthen the proposal further with optional features like:

A. Typed Parameter Contracts

[
   StartDate = type date,
   CustomerId = Int64.Type
]

to ensure providers bind correctly.

B. Safe Dynamic Lists

Support:

WHERE Region IN @RegionList

with structured array binding instead of string expansion.

Why This Matters

This capability would:

• improve security
• reduce SQL injection risk
• improve DirectQuery performance
• support enterprise governance
• enable vendor-managed read-only environments
• align Power BI with modern parameterized query behavior available in other BI/data platforms