none
Execute Stored Procedure SQL Statement with Parameter from Cell RRS feed

  • Question

  • I would like to know if there is a way to pass the parameters to SQL Statement of Power Query. For example, I have a SQL Statement as follows:

    EXEC [dbo].[spReportBuilder]
    @Report = N'Revenue Summary',
    @Year = N'2014',
    @Period = N'11'

    I would like to know if it is possible to pass the @Report, @Year, @Period values from cells in the workbook, preferably without vba.

    Thanks

    Sunday, August 17, 2014 4:38 PM

Answers

  • rtisserand, 

    Here is the M code: 

    let
        IDValue = Excel.CurrentWorkbook(){[Name="YearTable"]}[Content],
        Source = Sql.Database("localhost", "AdventureWorks2012", [Query="EXEC [dbo].[spReportBuilder] @Report = N'Revenue Summary', @Year = N'" & Number.ToText(IDValue[ID]{0}) & "', @Period = N'11'"])
    in
        Source

    Some notes:

    You have to reference the excel query from another step in the query or you will get the following error: 

    Formula.Firewall: Query <>something<> references other queries or steps and so may not directly access a data source. Please rebuild this data combination.

    I only did the parameter for one value.

    Hope this helps.

    Reeves


    Denver, CO


    Monday, August 18, 2014 6:45 AM

All replies

  • rtisserand, 

    Here is the M code: 

    let
        IDValue = Excel.CurrentWorkbook(){[Name="YearTable"]}[Content],
        Source = Sql.Database("localhost", "AdventureWorks2012", [Query="EXEC [dbo].[spReportBuilder] @Report = N'Revenue Summary', @Year = N'" & Number.ToText(IDValue[ID]{0}) & "', @Period = N'11'"])
    in
        Source

    Some notes:

    You have to reference the excel query from another step in the query or you will get the following error: 

    Formula.Firewall: Query <>something<> references other queries or steps and so may not directly access a data source. Please rebuild this data combination.

    I only did the parameter for one value.

    Hope this helps.

    Reeves


    Denver, CO


    Monday, August 18, 2014 6:45 AM
  • rtisserand,

    This was a good question and I decided to fully discus in a blog post. Too many step to fully outline here. 

    Power Query and Stored Procedures with Parameters

    Hope this helps.

    Reeves


    Denver, CO

    Tuesday, August 19, 2014 5:15 PM
  • Thank you for the great detail in the steps you posted on your blog. I was able to not only apply what you posted to a stored procedure, but also to a simple query against the database. Thank you for your help!

    TechNet responses.

    Tuesday, August 19, 2014 11:34 PM
  • I am still getting the formula.firewall error on my query even when I tried to reference the other query in a seperate step.

    M-code (didn't know how to get it properly formatted, because M was not listed as a language in the code drop-down):

    let
        Params = Parameters[Value],
        Source = Sql.Database("<servername>", "<databasename>", [Query="EXECUTE sp_name " &Number.ToText(Params{0}) &", " &Number.ToText(Params{1})])
    in
        Source

    Parameters is a query that references an Exceltable with 2 columns. It has labels and values as columns. Then I created a list Params and added the Value column to it. The next step references row 0 and row 1 from the Params list. However I still get the Formula.Firewall error.

    (I removed the servername, databasename and stored procedure name for security reasons)


    Monday, August 24, 2015 7:21 AM