In the present day I wish to clarify how one can go parameters to a SQL Server saved process. I actually was in search of a strategy 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 submit. 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 Residence 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 may 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. Identify 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 tackle the desk as beneath:

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 title of the column. So to adders the worth of the primary column of “param” desk, the above code might be as beneath:

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

and for the second it is going to be like this:

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

Now we have to exchange the constants from the question with the expressions above to make the question parameterised.  You possibly can copy the code beneath 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

It is advisable to put your individual SQL Server occasion title within the above code.

Be aware to the only citation marks within the code.

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

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

As an illustration, change the ProductID from 727 to 800 then refresh information. You’ll see the beneath display:

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

We’re carried out!