Running a SQL Server stored procedure from Excel

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:

Stored Procedure results

.. and this is how the users will see it in Excel (after manually adding a header row, and some formating on the figures):

End result

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'

Stored Procedure results

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.

Add a button icon

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.

Assign Macro screen

Click on the New button. You are then taken into Excel's beloved VBA scripting window.

Excel VBA 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 par As String
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

Dim prmCustomerID As ADODB.Parameter

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

Add a reference

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:

Run the stored procedure

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:

Run the stored procedure

A sample Excel file 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

blog comments powered by Disqus