Use Excel Parameters to Dynamically Export SQL Server Data


By:   |   Updated: 2020-05-08   |   Comments (1)   |   Related: More > Microsoft Excel Integration

Problem

Microsoft Query allows us to retrieve data from external sources into Excel easily, which can be very handy if we want to provide an ad-hoc report to our users. All we need is to copy-paste our T-SQL query code and hand the Excel file over to our users, who can refresh the dataset at any time.

However, sometimes we are required to add parameters to our report, and we usually gravitate to a different tool like Reporting Services, Power BI, Crystal Reports, etc. Not any more, with the power of the M language, we can add parameters into our Excel report and use them for our query. In this tip, I will show you how it's done.

Solution

In our example, I’ll use the AdventureWorks2017 database, and I’ll create a simple stored procedure to query the "Product" table between the selling start and sell end dates. Our task in this example is to pass different sell dates to the stored procedure, and our report will refresh accordingly, allowing the user to manipulate the dates parameters as they wish without modifying the query.

USE [AdventureWorks2017]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
--    Example: exec [AdventureWorks2017].dbo.ProductList '2011-05-01','2012-12-24'
	
CREATE PROCEDURE [dbo].[ProductList] @SellStartDate as Date, @SellEndDate as Date 
AS
BEGIN
 
   SET NOCOUNT ON;
 
   SELECT
        PR.[Name] ProductName
       ,[StandardCost]
       ,[ListPrice]   
       ,CAST([SellStartDate] AS DATE) SellStartDate
       ,CAST([SellEndDate] AS DATE) SellEndDate
   FROM [AdventureWorks2017].[Production].[Product] PR
   WHERE SellStartDate>[email protected] AND SellEndDate<[email protected]
   ORDER BY SellStartDate,ProductName
 
END

We will start with creating the parameter fields.

parameter fields

Then, we will highlight them and assign them a name, in this instance: "GetValue_1"

get value

Next, we will add the query from SQL Server, as described in the screenshot below.

sql server database

And fill in the required information for server and database. In the statement box, we will copy-paste an example.

exec [AdventureWorks2017].dbo.ProductList '2011-05-01','2012-12-24'
sql server database

When ready, click, OK. And we can click the "Load" button.

adventure works

It will load the results into a new sheet.

product name

Next, we need to modify the M language to find out the line of code used to generate the database call.

Therefore, we need to edit the query.

workbook queries

Note, if the above window is missing, go to Data > and click "Show Queries".

show queries

After clicking Edit, it will open the Power Query window.

query editor

Click the "Advanced Editor" button to see the code. Note, we will use this the editor window very frequently in this tip.

transform

Below we can see the code being uses for the procedure call.

advanced editor

We will make a note of this line of code, as we will use it later.

Following the below steps, we will create the necessary M code. Unless you are an expert in M, I suggest you test each line of code until you achieve the desired M code. The best way to do that is to output the result variable of each step.

Please note the following if you are unexperienced with M.

  1. A block of code most have "LET" and "IN".
    1. The "LET" section will include all our steps in will be evaluated in sequence
    2. The "IN" section is used for outputting the result
  2. It is recommended that each operation have its own line of code, and it has to include a variable name at the beginning. And it must terminate with a comma unless it’s the last line in the "LET" block.
  3. The M language evaluation concepts:
    1. M is using "Lazy Evaluation", which means each line of code is evaluated as needed. If the line of code is not required for the output, it will not be calculated.
    2. All other expressions are using "Eager Evaluation", which means that they are evaluated immediately when encountered during the evaluation process.

In order to build the M code, we will create a Blank Query and slowly build the required code.

show queries

We will click the "Advanced Editor".

transform

In the following code, we will pull the parameter values from the spreadsheet into a variable.

let
    Source = Excel.CurrentWorkbook(){[Name="GetValue_1"]}[Content]
in
    Source			
query
excel

Next, we need to pull the date value into a variable. If we right-click on the Date field and select "Drill Down", we will get the required code. To view the code, click on the Home tab > Advanced Editor.

advanced editor
convert
advanced editor

Now, we need to convert the date to text with the format "yyyy-mm-dd". To do so, we can apply the function "ToText" from the DateTime library, more details are available here.

current workbook
date time

Now that we have the Sell Date, we can get the End Date value the same way.

current workbook
end date

Now, let’s create the required stored procedure call as a variable called "query". We can use the same common operators like in Excel to create the required call.

query = "exec ProductList '"& SellDate &"','"& EndDate &"' "			
current workbook
product list

Now, let’s copy the query line generated by M from Query 1. We need to modify it accordingly, instead of passing the procedure call, we will pass the "query" variable.

let
    Source = Excel.CurrentWorkbook(){[Name="GetValue_1"]}[Content],
    SellDate = DateTime.ToText(Source{0}[SellDate],"yyyy-MM-dd"),
    EndDate = DateTime.ToText(Source{0}[EndDate],"yyyy-MM-dd"),
    query = "exec ProductList '"& SellDate &"','"& EndDate &"' ",
    target = Sql.Database("ohad\dbtesting", "AdventureWorks2017", [Query=query])
in
    target			

Now, click "Done", and you will be required to approve the following screens for the Privacy level.

sql database
privacy levels
power query editor

Next, click on "Close & Load" and from the drop-down menu, select "Close & Load To..."

power query editor

We can load the data to the same worksheet, by selecting "Existing worksheet" and specifying the cell where we would like the table to start.

load to

Then we should get the results, as in the screenshot as shown below.

sell date

Now, if we want to change the parameters, we can modify the dates, and go to Data > Refresh All to refresh the data. Or, hit Ctrl + Alt + F5 on the keyboard.

show queries

Note, after a hitting refresh it might ask you to approve to run the query. Just click Run.

native database query

Additional Resources

Next Steps


Last Updated: 2020-05-08


get scripts

next tip button



About the author
MSSQLTips author Ohad Srur Ohad Srur is a Microsoft Solution Expert (MCSE) - Data Platform and the managing director of Data-Best, a consulting company that focuses on assisting to K-12 independent schools.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, May 08, 2020 - 3:50:42 AM - Pavel Back To Top

Hello Ohad, nice article, thanks!
Though the title is a little bit ambiguous, the more appropriate would be something like "Use Excel fields as SQL stored procedure parameters to import data"



download


Recommended Reading

Passing Dynamic Query Values from Excel to SQL Server

How to Create a Burndown Chart in Excel from Scratch

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

How to Export Data from SQL Server to Excel

Insert, Update or Delete Data in SQL Server from Excel





get free sql tips
agree to terms


Learn more about SQL Server tools