Right from the beginning let’s be clear. Even though this process is possible, don’t use it as your standard process. Instead use How to Execute SQL Query in Excel – Tutorial with Examples. But I will show you anyway. Processing SQL queries through procedures in Excel is not optimal in general, but sometimes there is no other option. If you are a power user this guide might be handy for Excel reports automation. So let’s have a look on how to execute a SQL server procedure in excel.

Creating a Procedure in SQL Server for future use in Excel

Let us create simple procedure for demonstrative purposes. It returns monthly sales. Input parameters for this procedure will be:

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

The Goal is to call the procedure with parameters inside Excel in a way that it will return only results for time period we want.

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;

The procedure works OK

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

SQL v Excelu

Connecting SQL Procedure to Excel and setting parameters in cells

1) First thing will be setting up Excel. Open a new workbook/sheet and prepare entry parameters for the procedure (DATE FROM and DATE TO).  This parameters can be changed later and the procedure will return results based on that.

excel parameters for SQL server procedure

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

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

3) Select New data source

microsoft query - how to execude procedure in excel

4) Give it some name like “localhost connection) and as a driver select “SQL Server Native Client 11” (or “ODBC Driver 11 for SQL Server”). Then 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 – never use login and password method in Excel imports for security reasons. Instead use Windows auth.

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

6) After that, confirm all windows 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

Call SQL procedure in Excel

8) On the following display, click on button SQL (marked red) and write following into the SQL Query. Question marks represent SQL procedure inputs parameters. We will connect them to the Excel cells (step 1) later in step 10

{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 are in following window now:

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

10) Select OK. After that Excel wants to link cells with parameters to our script inserted earlier in step 8. 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 imports data and the SQL procedure is set in Excel.

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

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

In case you want to dive into SQL language deeper, please check SQL Tutorials category on my blog where you can find a page providing you a navigation to your learning journey.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and 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.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

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