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)ASBEGIN 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'
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.
2) Next, click on Data – Get Data – From Other Sources – From Microsoft Query
3) Select New data source
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”.
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.
6) After that, confirm all windows 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
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 (?,?)}
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:
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).
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.
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.
