Forum Discussion

chinma's avatar
chinma
Frequent Visitor
2 years ago

Calling System-i Stored Procedure as Direct Query

Hello Team,

We are tyring to call DB2 (System-i) stored procedure as Direct Query in Power BI Query Editor but not sure how to use dynamic parameters.  Here is the snapshot of the error message we are getting:

Any help would be appreciated.

Thank you.

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

    Hi chinma ,

     

    Not sure if this will solve your problem exactly as I don't know whether this works for calling Stored Procs, but here's how to use dynamic parameters against a DB2 source:

     

    -1- Create parameters in Power Query/Dataflow. Lets call them 'Parameter1' and Parameter2'.

    -2- Add parameter placeholders to source code:

    let
    Source = DB2.Database("SERVER", "DATABASE"),
    NativeQuery = Value.NativeQuery(
        Source,
        "SELECT Items, Values
        FROM Table
        WHERE
            Field1 <= ?  -- Parameter1 placeholder
            AND Field2 > ?  -- Parameter2 placeholder",
        {Parameter1, Parameter2}  -- List of Parameter names in the order used in SELECT statement
    )
in
    NativeQuery

     

    Now, when you run the DB2 query, the parameter values will be picked up and inserted into the DB2 query before sending to the source.

     

    Pete