How to Run a Stored Procedure with Parameters using a Function in Power Query

How to Execute a Stored Procedure with Parameters using a Function in Power Query

Scenario:
Lets say there is a Stored Procedure sp_SalesByProduct with parameters as @ProductName, @StartDate and @EndDate, defined in the SQL Server.

Now we want to run this Stored Procedure in Power Query, by dynamically passing the list of values for the @ProductName and dynamic datetime values for @StartDate and @EndDate. 

Phase 1: Define a Function with Dynamic Parameter Values:

We can achieve above Scenario by Creating a function in Power Query, by passing the below code in a new Blank Query and then give the function name as "fn_SalesByProduct":

let

    /* Define a function that calls the stored procedure sp_SalesByProduct */

    fn_SalesByProduct = (ProductName as text, StartDate as datetime, EndDate as datetime) =>

    let

        /* Format parameters for SQL Server */

        _StartDateVal = "'" & DateTime.ToText(StartDate, "yyyy-MM-dd HH:mm:ss") & "'",

        _EndDateVal = "'" & DateTime.ToText(EndDate, "yyyy-MM-dd HH:mm:ss") & "'",

        _ProductName = "'" & Text.Trim(ProductName) & "'",

        /* Construct EXEC statement for Stored Procedure */

        _Src_Qry = 

            "EXEC " & p_schema & ".sp_SalesByProduct " & 

            "@ProductName = " & _ProductName & ", " & 

            "@StartDate = " & _StartDateVal & ", " & 

            "@EndDate = " & _EndDateVal,

        /* Execute the stored procedure against the SQL Server Database */

        Source = Sql.Database(p_server, p_database, [Query = _Src_Qry])

    in

        Source

in

    fn_SalesByProduct

Notes:
The Power Query parameters p_server , p_database, p_schema, will hold the values for the Server, Database and Schema names of the SQL Server data source.

The values for the function parameters ProductName, StartDate, EndDate will be passed from the below step in the Invoke Function Query.

Phase 2: Invoke the Function to generate the Dataset with Results:

Now we will invoke the above function to generate a dataset fact_SalesByProduct with results using the below Code:

fact_SalesByProduct:

let

    _TodayDT = DateTime.Date(DateTime.LocalNow()),

    _StartDT =  DateTime.From(Date.StartOfMonth(Date.AddMonths(_TodayDT, -1))),

    _EndDT =  DateTime.From(Date.StartOfMonth(Date.AddMonths(_TodayDT, 0))),

    

/* List of Product Names to be used in the query to run by Function */

    _List_ProductName = { "Radio", "Television", "Laptop" },

    /* Invoke the fn_SalesByProduct function for each value in the Products List */

    Function_Result = List.Transform(

        _List_ProductName,

        each try fn_SalesByProduct(_, _StartDT, _EndDT) otherwise #table({}, {})

    ),

    /* Combine the Function results for each Product into a single table */

    Combined_Result = Table.Combine(Function_Result)

in

    Combined_Result

Notes:
The values for the function parameters ProductName, StartDate, EndDate are dynamically defined with in this Invoke Query and will be pass back to the function:
fn_SalesByProduct(_, _StartDT, _EndDT)

This function will run for each (_) ProductName will be passed from _List_ProductName.

Please note: This may not be a better approach when we have a huge list of values to pass to the procedure. In this approach, for each parameter value, you may need to allow the permission to run the Native query.

You can try to automate this process from backed SQL Server to generate a View or a table with the required data for reporting.

However, you can use above logic just for reference for another scenarios, to pass Parameter values (Single Values instead of List of Values) dynamically to run a native SQL Query.

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------