Running a SQL Server stored procedure from Excel
By Mike Gledhill
In this walkthrough, we'll see how to call a Stored Procedure directly from Excel.
We'll add a Stored Procedure to Microsoft's sample Northwind database, get Excel to call it, passing it a parameter, then we'll display its results in the Excel worksheet. Here's what the stored procedure's results look like:
.. and this is how the users will see it in Excel (after manually adding a header row, and some formating on the figures):
Running a Stored Procedure from Excel 2007
First, let's create a simple Stored Procedure in the Northwind database, which takes a parameter (Customer ID) and returns some details of orders that this customer has placed.
CREATE PROCEDURE [dbo].[SP_GetOrdersForCustomer]
@CustomerID nchar(5)
AS
BEGIN
SELECT cst.[CustomerID],
cst.[CompanyName],
cst.[ContactName],
ord.[OrderID],
ord.[EmployeeID],
emp.[FirstName],
emp.[LastName],
ord.[ShippedDate],
prd.[ProductName],
od.[UnitPrice],
od.[Quantity]
FROM [Customers] cst,
[Orders] ord,
[Order Details] od,
[Employees] emp,
[Products] prd
WHERE ord.[CustomerID] = cst.[CustomerID]
AND emp.[EmployeeID] = ord.[EmployeeID]
AND od.[OrderID] = ord.[OrderID]
AND prd.[ProductID] = od.[ProductID]
AND cst.[CustomerID] = @CustomerID
ORDER BY cst.[CustomerID], emp.[EmployeeID], ord.[ShippedDate] DESC
END
In SQL Server Management Studio if I run this Stored Procedure using the following command, it returns the results that we saw earlier:
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
But, how do we get Excel to run this Stored Procedure ?
First, run Excel, and create a blank workbook.
Now, in cell B2, type in "Customer ID:", and in cell D2, type in "ALFKI" (both without the quotes). ALFKI is the customer code of one of Northwind's example customers. We will get Excel to read in the value of cell D2 and pass it to the Stored Procedure as the CustomerID parameter.
Next, we need to add a Button to our spreadsheet. Click on the Developer tab, then the Insert button, then click on the icon which looks like a button.
Now, hold down the left mouse button, and drag the outline of a button, somewhere around row 4 below our "Customer ID" label.
When you release the left mouse button, you'll see this dialog.
Click on the New button. You are then taken into Excel's beloved VBA scripting window.
Paste the following code between the Sub Button1_Click and End Sub lines.
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents
' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("CustomerID", adVarChar, adParamInput, 10, Range("D2").Text)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "SP_GetOrdersForCustomer"
Set rs = cmd.Execute(, , adCmdStoredProc)
' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
Application.StatusBar = "Data successfully updated."
This code does three things.
- Clears the "results area" of our worksheet, namely from cell B8 to the bottom of our worksheet.
- Creates a connection to our database, and calls our "SP_GetOrdersForCustomer" Stored Procedure.
- Pastes the results into our worksheet, from cell B8.
This script uses the Microsoft ADO libraries to connect to SQL Server, so we need to tell Excel to include these libraries.
To do this, click on Tools then References.
Scroll down until you find the item "Microsoft ActiveX Data Objects 2.8 Libary" (or whichever version is most recent on your PC), tick the checkbox next to it, then click on the OK button.
You can now click on File, then "Close and return to Microsoft Excel".
If all goes well, now, when you click on your button, Excel will run the Stored Procedure, and display the results in cell B8 onwards:
How cool is that !
You can now add formatting and a header row to the worksheet to make it look more professional. You could start by giving "Button 1" a friendlier caption, by right-clicking on the button, select Edit text, and type in a friendlier caption.
With a bit of TLC, you can make your report look professional:
RunStoredProcedureFromExcel.xlsm
When you Save this Excel file, remember to save it as a "Excel Macro-Enabled Workbook (*.xlsm)" file, as it contains Excel VBA script.
Closing thoughts
So, do I recommend that you throw away your Silverlight and WPF books, and start writing applications using Excel and Stored Procedures from now on ?
Of course not. But I have found this trick particularly useful with major applications which have been tested and deployed to live environments, but then the user wants to see a little extra information from the database.
I don't really want to add extra screens to my application, then suffer having to go through the testing and deployment phase again.
Giving the users one-off reports, which they can run themselves like this, gives me a "quick win", without needing to make changes to my original app.
The other big advantage is that users love Excel (I'm sorry... but you know it's true).
And this gives us a really easy way to get raw data from SQL Server directly into Excel, ready for them to play with.
Useful links
Sample "Northwind" database for SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143221.aspx
Sample "Northwind" database for SQL Server 2000/2005
http://www.microsoft.com/download/en/details.aspx?id=23654
This installs the .mdf and .ldf database files into the directory: C:\SQL Server 2000 Sample Databases
Comments