I must say right from the beginning that even though this process is possible, I do not recommend it (but I will show it to you anyway) :). Launching SQL queries in Excel is not optimal in general, but sometimes there is no other option. This guide is aimed on advanced Excel users with SQL knowledge. It is not made for beginners or even slightly advanced users.

If you are a power user having analytics in Excel, this guide might be handy for Excel reports automation. NB if you are not keen on creating report in Power BI or PowerPivot. This is by the way hood choice for data import from a database.

Creating Simple Procedure in SQL Server

We will create primitive procedure for demonstrative purposes. It will return monthly sales. Entry parameters for this procedure will be:

• @DATE_FROM – date since when we want to count the sales
• @DATE_TO – date until when we want to count the sales

Goal is to call the procedure with parameters in a way that it will return only results for time period we want to observe. If you cannot do this, ask someone from IT 🙂

SQL procedure may look like this:

CREATE PROCEDURE Get_Sales (@DATE_FROM DATE, @DATE_TO DATE)
AS
BEGIN
  SELECT
    [b].[CalendarYear],
    [b].[MonthNumberOfYear],
    SUM([SalesAmount]) AS [SalesAmount]
  FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] [a] join [AdventureWorksDW2014].[dbo].[DimDate] [b]
        on [a].[OrderDateKey]=[b].[DateKey]
  WHERE [b].[FullDateAlternateKey] BETWEEN @DATE_FROM AND @DATE_TO
  GROUP BY
    [b].[CalendarYear],
    [b].[MonthNumberOfYear]
END;

…and we will test if procedure works OK:

EXEC dbo.Get_Sales '2013-01-01','2013-02-28'

Connecting SQL Procedure to Excel

1) First thing will be setting up Excel. We will open it and prepare entry parameters for the procedure. There will be 2 of them (DATE FROM and DATE TO).  This parameters can be changed later and the procedure will return results based on that.

2) Further, click on Data – Get Data – From Other Sources – From Microsoft Query

3) We will select New data source

4) We will name the new source “localhost connection) and as a driver select “SQL Server Native Client 11” (or “ODBC Driver 11 for SQL Server”). Then we will click on “3 Connect”.

5) Enter name of the SQL instance in Server field and use Trusted connection

6) After that, we will confirm all until we get to window “Query Wizard – Choose Columns” and select cancel.

7) Close next window “Add Tables”. We need to get to interface where we can write our own SQL query

8) On the following display, click on button SQL (marked red) and write following into the SQL Query:

{CALL AdventureWorksDW2014.dbo.Get_Sales (?,?)}

9) Then select OK and also confirm next 3 windows: a) Continue anyway… b) Parametr 1 leave blank c) Parametr 2 leave blank. We should get into following state after that:

10) Select OK and Excel will then want to link to cells with parameters. Set Parameter 1 to Date from (cell C2) and Parameter 2 to Date until (cell C3).

11) Then confirm and it is DONE. Excel loads data and the SQL procedure is set in Excel.

12) Now we have prepared Excel communicating with SQL Server database and it returns sales  based on dates in Excel cells C2 and C3