Energy Question and SQL Server Saved Procedures

Right now I need to clarify how one can go parameters to a SQL Server saved process. I personally was in search of a solution to go parameters to a SQL Server saved proc from Energy Question. I spent lots of time to seek for a superb article over the web that explains how I might go parameters to a saved proc from Energy Question and present the ends in Excel. However, I couldn’t discover that a lot data round this as I anticipated. So, I made a decision to do some work round and you may learn the outcomes on this put up. To simplify the answer, I’m going to make use of uspGetBillOfMaterials saved process in AdventureWorks 2012 database. The saved process accepts an integer quantity as ProductID and a date as CheckDate. So we have to go two parameters to uspGetBillOfMaterials  to get the outcomes.

If we execute the saved proc in SSMS utilizing

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

, we’ll get the next consequence:

Now, lets go to do some works on Energy Question. So open Microsoft Excel and go to Energy Question tab and choose SQL Server database.

Now kind Server, Database and SQL Assertion, then click on OK.

Choose a reputation for the question, I names it GetBOM. Then from Dwelling tab click on on “Shut & Load”.

To date we’ve loaded the outcomes of to Excel. Now we have to go the parameters to the saved proc. As you possibly can see, the above saved proc  accepts an integer and a date as parameters. So we create a desk within the Excel sheet with two columns that include the parameters. Title the desk as “param”.

To make out life simpler I modified the format cell of the “Verify Date” column to Textual content, different smart we’ll must convert it in Energy Question. We nonetheless must convert ProductID in Energy Question.

Now return to Energy Question, proper click on on GetBOM and click on Edit

In GetBOM Question Editor window, go to View tab and click on “Superior Editor”.

Right here we have to add some codes. The scripts in Energy Question are written in a language referred to as “M”.

All we want is to parameterise the question in order that we learn the contents of from the “param” desk we outlined earlier than. In M language, to learn a cell content material we have to handle the desk as under:

Excel.CurrentWorkbook(){[Name=”TABLE_NAME“]}[Content]{ROW_NUMBER}[#”COLUMN_NAME”],

Within the above code, TABLE_NAME is “param” in our pattern, ROW_NUMBER is the variety of row that we have to load its content material and COLUMN_NAME is the identify of the column. So to adders the worth of the primary column of “param” desk, the above code can be as under:

Excel.CurrentWorkbook(){[Name=”param“]}[Content]{0}[#”ProductID”],

and for the second will probably be like this:

Excel.CurrentWorkbook(){[Name=”param“]}[Content]{0}[#”Check Date”],

Now we have to change the constants from the question with the expressions above to make the question parameterised.  You may copy the code under within the Superior Editor:

let
    ProductID=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”ProductID”],
    CheckDate=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”Check Date”],
    Supply = Sql.Database(“SQL_SERVER_INSTANCE NAME“, “AdventureWorks2012”,
    [Query=”exec [dbo].[uspGetBillOfMaterials] ‘”
    & Quantity.ToText(ProductID)
    & “‘, ‘”
    & CheckDate
    & “‘”])
in
    Supply

You must put your individual SQL Server occasion identify within the above code.

Be aware to the one citation marks within the code.

To concatenate texts we use “&” in M language. Click on Finished then click on Shut and Load from Dwelling tab.

Now in the event you change the values of the “param” desk and refresh knowledge you’ll see the brand new ends in Excel.

For example, change the ProductID from 727 to 800 then refresh knowledge. You’ll see the under display screen:

As you possibly can see the primary parameter to go to saved process is modified to 800. Click on RUN to see the ends in Excel.

We’re accomplished!