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'

SQL v Excelu

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.

how to execute sql server procedure from excel

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

microsoft query (data ribbon) - how to execude procedure in excel

3) We will select New data source

microsoft query - how to execude procedure in excel

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”.

microsoft query - create new datasource - how to execude procedure in excel

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

microsoft query - create new datasource (login) - how to execude procedure in excel

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

microsoft query - canceling query wizard - how to execude procedure in excel

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

microsoft query - canceling add tables - how to execude procedure in excel

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

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

microsoft query - sql query call procedure - how to execude procedure in excel

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:

microsoft query - sql query settings - how to execude procedure in excel

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).

microsoft query - sql query setting procedure parameters - how to execude procedure in excel

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

microsoft query - final result - how to execude procedure in excel

Rate this post

Ing. Jan Zedníček - Data & Finance

My name is Jan Zedníček and I work as a freelancer. I have been working as a freelancer for many companies for more than 10 years.
I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

Leave a Reply

Your email address will not be published. Required fields are marked *